Tuesday, August 11, 2009

ROLLUP and ORDER BY

In one of my previous posts, I discussed on how useful GROUPING function is while writing a ROLLUP/CUBE query.  One more way GROUPING function can help you is – in ordering the results returned by ROLLUP/CUBE queries.

The reason I’m writing this post is, sometime back I seen somebody writing a weird ORDER BY statement to get the desired ordering after writing a query using a ROLLUP operator.  First he didn’t use the GROUPING function in SELECT statement and 2nd his ORDER BY was something like :

ORDER BY CASE ColumnName
            WHEN 'Total Of ColumnName'
            THEN 'Zzzzzzzz'
            ELSE ColumnName
         END

Just to get sub total returned by ROLLUP at the bottom of result set.  Well of course if he had the knowledge about GROUPING then he wouldn’t have written such CASE statement in ORDER BY.

As you may know that GROUPING function returns 1 when the row is added by either the CUBE or ROLLUP operator, and 0 when the row is not the result of CUBE or ROLLUP.  So you can easily use this property of the GROUPING for ordering the result set.

Lets have a look at the following example, 1st create some sample data.  

-- create sample table Sales

CREATE TABLE Sales
(
ID INT,
FName VARCHAR(30),
Zone VARCHAR(30),
Sale INT
)
GO

-- Load sample data

INSERT INTO Sales SELECT
1, 'Mangal', 'East', 20 UNION ALL SELECT
2, 'Mangal', 'East', 150 UNION ALL SELECT
3, 'Mangal', 'West', 50 UNION ALL SELECT
4, 'Ram', 'East', 45 UNION ALL SELECT
5, 'Ram', NULL, 80 UNION ALL SELECT
6, 'Ram', NULL, 40 UNION ALL SELECT
7, 'Sachin', 'West', 50 UNION ALL SELECT
8, 'Sachin', 'West', 40
GO

-- Test sample data

SELECT Id, FName, Zone, Sale
FROM Sales
GO

The sample data :

ID FName Zone Sale
1 Mangal East 20
2 Mangal East 150
3 Mangal West 50
4 Ram East 45
5 Ram NULL 80
6 Ram NULL 40
7 Sachin West 50
8 Sachin West 40

And here is the expected output :

FName Zone Total
Mangal East 170
Mangal West 50
Mangal All Zone 220
Ram East 45
Ram Unknown 120
Ram All Zone 165
Sachin West 90
Sachin All Zone 90
All Names All Zone 475

As you can see in the expected output, all the FNames are ordered in ascending order and their total SUM is at the bottom, same for the Zone column.  For ordering the result in that way just use the GROUPING(column_name) in ORDER BY just before the column_name.  See the following query, esp the ORDER BY clause:

SELECT CASE GROUPING(fname)
        WHEN 1 THEN 'All Names'
        ELSE ISNULL(Fname, 'Unknown')
        END AS FName,
        CASE GROUPING(Zone)
        WHEN 1 THEN 'All Zone'
        ELSE ISNULL(Zone, 'Unknown') END as Zone,
        SUM(Sale) AS Total
FROM Sales
GROUP BY Fname, Zone WITH ROLLUP
ORDER BY  GROUPING(fname),FName,GROUPING(Zone),Zone

Simple, isn’t it?  Now you don’t need to write a CASE statement in ORDER BY, just use the GROUPING function.  If you will be doing the ORDERING in application layer, then you will need to get in the GROUPING(fname) and GROUPING(zone) column in the SELECT list as well.

Mangal

7 comments:

  1. Hi,
    thanks for very nice post. I would like to ask if there were any changes to GROUPING function in MS SQL Server 2008 and if the same goal can be achieved with new feature GROUPING SETS (for example naming of aggregation row).

    Thanks again.
    Vladimir

    ReplyDelete
  2. Vladimir,

    Thanks for the comments. Yes, GROUPING function is also behave the same way for GROUPING SETS it does for ROLLUP n CUBE.

    Mangal

    ReplyDelete
  3. Thanks Mangal this post is very helpful and very easy to understand. Thanks alot.

    Abdul

    ReplyDelete
  4. Very helpfull article. You solved my zzz problem.

    When you're grouping on a number column, you'll get this error: "Syntax error converting the varchar value 'All name' to a column of data type int."

    To prevent this, convert the select column to a varchar, but order by the original table value:

    SELECT
    CASE GROUPING(nr) WHEN 1 THEN 'All nr' ELSE CAST(nr AS VARCHAR) END AS group_nr,
    COUNT(1) as [count]
    FROM some_table t
    GROUP BY id_cmd WITH ROLLUP
    ORDER BY GROUPING(nr), t.nr ASC

    ReplyDelete
  5. In my example I meant: GROUP BY id_cmd WITH ROLLUP

    ReplyDelete
  6. SELECT *
    FROM
    (
    SELECT country, sum(id) as cnt
    FROM mygroup GROUP BY country WITH rollup
    ) t
    ORDER BY cnt;

    ReplyDelete
  7. Great post with a lot of information about roll up, thanks!

    ReplyDelete