SQL Server - Sum entire column AND Group By

Question

Suppose I had the following table in SQL Server:

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 what I want is to get an output which would basically be:

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

So, given the following is true:

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 have to look like??

Thanks!!

Answer 1

You should be able to use something like this which 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

The result is:

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

Answer 2

I completely agree with @bluefeet's response -- this is just a little more of a database-independent 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.