Konrad Viltersten Konrad Viltersten - 3 months ago 7
SQL Question

Calculate the ratios of sub-sums in the total sum

I've poked around and found a number of different examples, none of which seemed to work. The closest one I've got was over() but it failed due to syntax error.

Consider the following query.

select Id,
count(*) as Count,
sum(Amount) as Sum
from MyTable
group by Id


The result is a list of Ids with the number of occurrences and the amounted values. This works great. However, I'd also like to show how large portion each sum is in the grand total. I've tried the following.

select Id,
count(*) as Count,
sum(Amount) as Sum,
sum(Amount) / sum(Amount) over() as Ratio
from MyTable
group by Id


It fails and I'm not sure how to remedy it (mostly because the over thingy was more of a guess than stable knowledge).

rcs rcs
Answer

You need to retrieve the total amount for all records, which you can do with the following:

SELECT sum(Amount) FROM MyTable

and put that as a divisor to the sum(Amount) in your query. So your query will look like below:

select   Id, 
         count(*) as Count, 
         sum(Amount) as Sum,
         sum(Amount) / (SELECT sum(Amount) FROM MyTable) as Ratio
from     MyTable
group by Id

If your Amount data type is INT, you may need to cast the Ratio as a decimal data type. For example:

select   Id, 
         count(*) as Count, 
         sum(Amount) as Sum,
         CAST (sum(Amount) AS NUMERIC(10,2)) / (SELECT sum(Amount) FROM MyTable) as Ratio
from     MyTable
group by Id

[EDIT]: If you want to use OVER clause, I would suggest you to have one more subquery:

SELECT   [Id], [Count], [Sum], [SUM] / SUM([SUM]) OVER (PARTITION BY 1)
FROM
(
         select   Id,
                  count(*) as Count, 
                  sum(Amount) as Sum            
         from     MyTable
         group by Id
) as t
Comments