A neat new feature in SQL Server 2008 is the GROUPING SETS clause, which allows you to easily specify combinations of field groupings in your queries to see different levels of aggregated data. Today we'll look at how you can use the new SQL Server 2008 GROUPING SETS clause to aggregate your data.
use tempdb
go
create table Customer(customerid int, year int, sales money)
--data insertion
insert into customer values
(1, 2005, 12000),
(1, 2006, 18000),
(1, 2007, 25000),
(2, 2005, 15000),
(2, 2006, 6000),
(3, 2006, 20000),
(3, 2007, 24000)
--Before SQL Server 2008 using Group By and Union All
select customerid, null [year], sum(sales) [total sales]
from Customer
group by customerid
union all
select null [customerid], [year], sum(sales) [total sales]
from Customer
group by [year]
union all
select null [customerid], null [year], sum(sales) [total sales]
from Customer
--With SQL Server 2008, using Grouping Sets
select customerid, [year], sum(sales) [total sales]
from Customer
group by grouping sets ((customerid), (year), ())
I think this is helpful