Calculate amount percentages

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.