devxlogo

Percentage of whole, upon a group

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 summarywherec.type = summary.typedrop table #access
See also  How to Create and Deploy QR Codes Online: A Comprehensive Guide
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist