Question:
Is it possible to display a grouped column as a percentage of the whole? Say I have a table that has two fields:

  ACCESS_TYP     CHRG-------------------------     10          1.00     10          2.00     11         45.00     10          7.00     11          5.00

But I want to display it as:

  ACCESS_TYP     summary    percentage  whole----------------------------------------------     10           10.00       16.66      60.00     11           50.00       83.33      60.00 

The “whole” field isn’t necessary, but I figured it had to be in there to get the percentage.

I can build up a query like so:

SELECT access_typ,       (select sum(chrg_amt) from acctsum),       sum(chrg_amt) FROM   acctsum GROUP  BY ACCESS_TYP

but dividing the one sum by the big sum doesn’t seem to work.

Answer:
To create a result set that contains both detail and summary information on the same line, you need to use what is termed a derived table. Basically, in the FROM clause you put the select statement that represents your summary information and provide it with a name. You can then join between your derived table and the detail information to provide the result set you need. Following is some sample code that illustrates the point.

create table #access(	type int,	charge float)insert into #access values (10,100)insert into #access values (11,100)insert into #access values (11,200)select c.type, c.charge, 'percentage' = round((charge/ summary.total) * 100,2)from#access c, (select type,'total' = sum(charge) from #access group by type) as summarywherec.type = summary.typedrop table #access