Konrad Viltersten - 1 year ago 64
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).

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download