SQL 2005: Subtotals and Totals

Man this was hard in SQL 2000 and man this is easy in SQL 2005. Most people don't understand the PARTITION clause of a SELECT statement. Hopefully this sample will help you:

set nocount on

-- table

declare @table table 
    (name varchar(1), rank varchar(1), salary tinyint)

-- data

insert into @table select 'a', 'a', 1
insert into @table select 'b', 'a', 2
insert into @table select 'c', 'b', 3
insert into @table select 'd', 'b', 4
insert into @table select 'e', 'b', 5

-- query

select 
     name
    ,rank
    ,salary 
    ,sum(salary) over (partition by rank) as subtotal
    ,sum(salary) over (partition by null) as total
from 
    @table

-- output

/*

name rank salary subtotal    total
---- ---- ------ ----------- -----------
a    a    1      3           15
b    a    2      3           15
c    b    3      12          15
d    b    4      12          15
e    b    5      12          15

*/