Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 Group By Sets


By:   |   Updated: 2007-10-15   |   Comments   |   Related: More > T-SQL

With Sql 2008 (in the current CTP you have in your hand), thanks to a new extension to the group by clause referred to as 'grouping sets', you now have the ability to use sets of grouping columns in your group by clauses, allowing you to define basically multiple groupings in the same single query - i.e., instead of providing a single group by column-set, you can define multiple 'sets' of grouping columns, and have the resultset include the concatenated results for each set (think of this as effectively the UNION ALL of multiple identical select statements that are grouped by different column sets).

Some sample code showing some simple usage is here below, enjoy!

  use tempdb;
  go
  if object_id('dbo.tblGroupTest') > 0
   drop table dbo.tblGroupTest;
  create table dbo.tblGroupTest (id int, year smallint, quarter smallint, month smallint, amt bigint);
  go
  -- Fill up some sample yearly, quarterly, monthtly data for 2000 - 2007...
  declare @i int
  set @i = 2000
  while @i <= 2007 begin
   insert dbo.tblGroupTest (id, year, quarter, month, amt)
   select row_number() over (order by a.object_id),
     @i,
     ntile(4) over (order by a.object_id),
     ntile(12) over (order by a.object_id),
     a.object_id
   from (
     select object_id from sys.columns
     union all
     select object_id from sys.columns
     ) a;
 
   set @i = @i+1;
  end
  go
 
  -- Report
  select year,
    case when grouping(quarter) = 1 then '-- TOTAL --' else cast(quarter as varchar(5)) end as quarter,
    case when grouping(month) = 1 then '-- TOTAL --' else cast(month as varchar(5)) end as month,
    sum(amt) as sumAmt, avg(amt) as avgAmt
  from dbo.tblGroupTest
  group by grouping sets (
   (year, quarter, month),
   (year, quarter),
   (year)
  )
  order by year, isnull(quarter,10), isnull(month,15);


Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.

 



Last Updated: 2007-10-15


get scripts

next tip button



About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools