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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: