Jerry Nixon on Windows: SQL 2005: Subtotals and Totals

Jerry Nixon on Windows

Thursday, July 3, 2008

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

*/