Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Aug 17, 1998

Percentage of whole, upon a group

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 summary
where
c.type = summary.type

drop table #access
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date