Q

Calculate amount percentages

I have a table with two columns, ID and amount. I need to calculate percentage amount allocated to an ID.

I have a table with two columns, ID and amount. I need to calculate percentage amount allocated to an ID.

It would be nice if we could simply say:

select id
     , pct(amount) as pct_amt
  from daTable
group
    by id

Unfortunately, there is no such PCT function. However, we can take a cue from a similar function, AVG. To calculate the ID averages, we can do this:

select id
     , avg(amount) as avg_amt
  from daTable
group
    by id

But we can also calculate the averages this way:

select id
     , sum(amount)
       / count(*) as avg_amt
  from daTable
group
    by id

In the above query, for each ID, we divide the total amount for that ID by the number of rows for that ID, to obtain the average.

In a similar way, we can calculate a percentage:

select id
     , ( 100.0 * sum(amount) )
         / ( select sum(amount)
               from daTable ) as pct_amt
  from daTable
group
    by id

In this query, we divide the total amount for each ID by the total amount for all IDs, i.e., the total for the entire table. Note that the sum(amount) in the dividend (the number being divided, i.e. on the top of the division) is for each individual ID, while the sum(amount) in the divisor (the dividing number, i.e. on the bottom of the division) is for all IDs. The divisor is a non-correlated subquery, which means that it can be evaluated once by the optimizer before the query starts, and then used in the calculation for each ID.

In the calculation of a percentage, we multiply the division by 100.0 to give the desired percentage number, and we sneak this into the calculation before the division, to ensure we get an accurate result. This is because sometimes the SUMs involved are integers, and division of one integer by another integer results in an integer answer, which means rounding. When we multiply the dividend by 100.0 first, this gives an intermediate decimal result, and then the division will yield a decimal number without rounding.

This was first published in September 2005
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close