Systems Engineering and RDBMS

GROUPING SETS in SQL Server 2008

Posted by decipherinfosys on November 3, 2007

Yesterday, we had covered the GROUP BY clause and the usage of the ROLLUP(), CUBE(), GROUPING() and GROUPING_ID() functions within the GROUP BY clause. In today’s post, we will look at one of the enhancements in this are in SQL Server 2008. The enhancement is called GROUPING SETS. This clause let’s you specify multiple grouping sets in the same query. You can achieve the same functionality in prior versions using multiple queries and using a UNION ALL clause to club the result sets together. With GROUPING SETS though, you can accomplish the same thing with much lesser code and the query will be more efficient as well. This can be very useful for reporting purposes when you want to visualize the data distribution based on different sets of groups.

Considering the same example as we did in yesterday’s post (see yesterday’s post to get the DDLs for the example):

If we want to see the effect on the totals for these groups:

COL1, COL2
COL2
COL1
No Grouping

then, we can re-write the SQL to be:

SELECT COL1, COL2, COUNT(*) AS CNT_RECORD, SUM(COL3) TOTAL_VAL FROM TEST_TBL GROUP BY COL1, COL2
UNION ALL
SELECT NULL AS COL1, COL2, COUNT(*) AS CNT_RECORD, SUM(COL3) TOTAL_VAL FROM TEST_TBL GROUP BY COL2
UNION ALL
SELECT COL1, NULL AS COL2, COUNT(*) AS CNT_RECORD, SUM(COL3) TOTAL_VAL FROM TEST_TBL GROUP BY COL1
UNION ALL
SELECT NULL AS COL1, NULL AS COL2, COUNT(*) AS CNT_RECORD, SUM(COL3) TOTAL_VAL FROM TEST_TBL
GO

This will yield:

COL1        COL2       CNT_RECORD  TOTAL_VAL

 ----------- ---------- ----------- -----------

 1           A          3           60

 2           A          2           130

 1           B          1           90

 3           C          2           230

 NULL        A          5           190

 NULL        B          1           90

 NULL        C          2           230

 1           NULL       4           150

 2           NULL       2           130

 3           NULL       2           230

 NULL        NULL       8           510

In SQL Server 2008, you can achieve this very easily by using GROUPING SETS functionality:

SELECT COL1, COL2, COUNT(*) AS CNT_RECORD, SUM(COL3) TOTAL_VAL
FROM TEST_TBL
GROUP BY GROUPING SETS
(
(COL1, COL2),
(COL2),
(COL1),
()
)
GO

COL1        COL2       CNT_RECORD  TOTAL_VAL

 ----------- ---------- ----------- -----------

 1           A          3           60

 2           A          2           130

 NULL        A          5           190

 1           B          1           90

 NULL        B          1           90

 3           C          2           230

 NULL        C          2           230

 NULL        NULL       8           510

 1           NULL       4           150

 2           NULL       2           130

 3           NULL       2           230

It is the same data just ordered differently because of the grouping. The different grouping sets are separated by a comma and the different elements of a grouping set are also separated by commas. In addition, one can have multiple GROUPING SETS in the same GROUP BY clause.

One Response to “GROUPING SETS in SQL Server 2008”

  1. […] from a previous posts in which we had covered some other T-SQL features of SQL 2008 (here and here), this table has three columns: COL1, COL2 and COL3 and this set of […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: