Tags

, ,


ROLLUP and ORDER BY

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.

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, ‘Jitendra’, ‘East’, 20 UNION ALL SELECT
2, ‘Jitendra’, ‘East’, 150 UNION ALL SELECT
3, ‘Jitendra’, ‘West’, 50 UNION ALL SELECT
4, ‘Rama’, ‘East’, 45 UNION ALL SELECT
5, ‘Rama’, NULL, 80 UNION ALL SELECT
6, ‘Rama’, NULL, 40 UNION ALL SELECT
7, ‘Priya’, ‘West’, 50 UNION ALL SELECT
8, ‘Priya’, ‘West’, 40
GO

— Test sample data

SELECT Id, FName, Zone, Sale
FROM Sales
GO

The sample data : ID FName Zone Sale
1 Jitendra East 20
2 Jitendra East 150
3 Jitendra West 50
4 Rama East 45
5 Rama NULL 80
6 Rama NULL 40
7 Priya West 50
8 Priya West 40

And here is the expected output : FName Zone Total
Jitendra East 170
Jitendra West 50
Jitendra All Zone 220
Rama East 45
Rama Unknown 120
Rama All Zone 165
Priya West 90
Priya 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.

Enjoy!

Advertisements