GROUP BY GROUPING SETS - KATMAI

IN SQL 2008 KATMAI, there new extension for group by clause. Which is GROUPING SETS. Its provides you to create sets of columns for grouping from you group by column. For example if you have having group on year, month its allow you to group first on year+month then year as single group, which means now you can do multiple grouping in the single query!

Lets see the example. We are having data for Sales. We need sales for each Month as well as each Year. So each year will have again sum of Month. Here is the pictorial representation of last statement.

So our data:



We need this as output:



In SQL 2000/2005 We may need CURSOR or TEMPORARY TABLE or TABLE VARIABLE, and I am sure that will be tedious job. So lets see how we can achieve this functionality in KATMAI using GROUPING SETS. Here is the small and sweet query :)

SELECT 
    S_Year, S_Month, SUM(S_Amt) as AmountSum, AVG(S_Amt) AvgAmt
FROM @Sales
GROUP BY GROUPING SETS ( 
    (S_Year, S_Month)
    ,(S_Year)
    )
ORDER BY S_Year

You can see the new keyword GROUPING SETS which has more then one Group Clause. This indicates that first you do Group on S_Year + S_Month then you Group it on S_Year.

Lets see the output.



You see the NULL in S_Month? That indicates the sum on months for particular Year mean its in the S_Month grouping. And KATMAI provides one function GROUPING which indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified. Read more on span class="keyword">GROUPING. Lets change query to get proper result.

SELECT 
    CASE 
        WHEN 
            GROUPING(S_Month) = 1 THEN 'Year Total: ' + CAST(S_Year AS VARCHAR(5)) 
        ELSE 
            'Month : ' + CAST(S_Month as VARCHAR(3)) 
        END 
    AS 'Grouping',
    
    SUM(S_Amt) as AmountSum, AVG(S_Amt) AvgAmt
FROM @Sales
GROUP BY GROUPING SETS ( 
    (S_Year, S_Month)
    ,(S_Year)
    )
ORDER BY S_Year, ISNULL(S_Month,15);

And lets see the output.



Download the SQL statements from here.

Check the Origional post

Its very useful while creating Reports.