SQL Server - Summarize Entire Column AND Group By

Question

Suppose SQL Server had the following table:

grp: val: criteria: a 1 1 a 1 1 b 1 1 b 1 1 b 1 1 c 1 1 c 1 1 c 1 1 d 1 1 

Now I want to get a result that will be basically:

 Select grp, val / [sum(val) for all records] grouped by grp where criteria = 1 

So, considering the following:

 Sum of all values = 9 Sum of values in grp(a) = 2 Sum of values in grp(b) = 3 Sum of values in grp(c) = 3 Sum of values in grp(d) = 1 

The output would be as follows:

 grp: calc: a 2/9 b 3/9 c 3/9 d 1/9 

What would my SQL look like?

Thanks!!

Answer 1

You should use something like this that uses sum() over() :

 select distinct grp, sum(val) over(partition by grp) / (sum(val) over(partition by criteria)*1.0) Total from yourtable where criteria = 1 

See SQL Fiddle with Demo

Result:

 | GRP | TOTAL | ------------------------ | a | 0.222222222222 | | b | 0.333333333333 | | c | 0.333333333333 | | d | 0.111111111111 | 

Answer 2

I totally agree with @bluefeet's answer - this is a bit more database-based approach (should work with most RDBMS):

 select distinct grp, sum(val)/cast(total as decimal) from yourtable cross join ( select SUM(val) as total from yourtable ) sumtable where criteria = 1 GROUP BY grp, total 

And here is the SQL Fiddle .