Using GROUPING and the WITH ROLLUP operator to create sub totals

 There are many occasions where you want to have subtotals appear on your query results, and using the GROUPING function along with the ROLLUP or CUBE operator I have found it to be an excellent way to accomplish that. This is especially useful when trying to export your results in a Text or CSV/Excel file, without having to rely on a reporting tool or keep variables for running totals in your program. Let’s take a look at a typical query using SUM to totals products sold and sales for a sample store.

SELECT pc.Name                      AS ProductName,   p.Color                           AS ProductColor,   SUM(sod.OrderQty)                 AS OrderQty,   SUM(sod.orderqty * sod.UnitPrice) AS Sales FROM Sales.SalesOrderDetail sod,   Production.Product p,   Production.ProductCategory pc WHERE sod.ProductID             = p.ProductID   AND p.ProductSubcategoryID    = pc.ProductCategoryID GROUP BY pc.name, p.Color ORDER BY pc.name, p.Color

 Output:

ProductName	ProductColor	OrderQty	Sales
Bikes	Silver	13271	17093187
Bikes	Black	15050	19529109
Clothing	Yellow	6295	6119206
Clothing	Blue	8456	8425868
Components	Yellow	11762	10452007
Components	Red	18088	19820314
Components	Black	17346	13706122

    Now let’s see how we can improve this query, by adding a WITH ROLLUP operator and GROUPING columns. By adding the WITH ROLLUP operator to the GROUP BY clause, we have added totals to the two columns we are grouping by, pc.Name and p.Color. The additional GROUPING columns help us identify the rows that represent a total. Rows where the ColorGroup is 1, and the NameGroup is 0, identify subtotals of the ProductName group. The last row where ColorGroup and NameGroup are 1 represents the query grand total.

SELECT pc.Name                      AS ProductName,   GROUPING(pc.Name)                 AS NameGroup,   p.Color                           AS ProductColor,   GROUPING(p.Color)                 AS ColorGroup,   SUM(sod.OrderQty)                 AS OrderQty,   SUM(sod.orderqty * sod.UnitPrice) AS Sales FROM Sales.SalesOrderDetail sod,   Production.Product p,   Production.ProductCategory pc WHERE sod.ProductID          = p.ProductID   AND p.ProductSubcategoryID = pc.ProductCategoryID GROUP BY pc.name, p.Color WITH ROLLUP

Output:

ProductName	NameGroup	ProductColor	ColorGroup	OrderQty	Sales
Bikes	0	Black	0	15050	19529109
Bikes	0	Silver	0	13271	17093187
Bikes	0	NULL	1	28321	36622296
Clothing	0	Blue	0	8456	8425868
Clothing	0	Yellow	0	6295	6119206
Clothing	0	NULL	1	14751	14545074
Components	0	Black	0	17346	13706122
Components	0	Red	0	18088	19820314
Components	0	Yellow	0	11762	10452007
Components	0	NULL	1	47196	43978443
NULL	1	NULL	1	90268	95145813

    Now let’s take it a step further. Using the grouping columns above, we can now replace the original ProductName and ProductColor columns with CASE statements, replacing the NULL values with the word “Total” to make it more efficient. As you can see we also incorporated the GROUPING function to the ORDER BY clause in order to get our results to sort properly and have the Total row appear on the bottom of each group as it should be.

SELECT   CASE GROUPING(pc.Name)     WHEN 0 THEN pc.Name     ELSE 'Total'   END                               AS ProductColor,   CASE GROUPING(p.Color)     WHEN 0 THEN p.Color     ELSE 'Total'   END                               AS ProductColor,   SUM(sod.OrderQty)                 AS OrderQty,   SUM(sod.orderqty * sod.UnitPrice) AS Sales FROM Sales.SalesOrderDetail sod,   Production.Product p,   Production.ProductCategory pc WHERE sod.ProductID          = p.ProductID   AND p.ProductSubcategoryID = pc.ProductCategoryID GROUP BY pc.name,p.Color WITH ROLLUP ORDER BY GROUPING(pc.name), pc.Name, GROUPING(p.color), p.Color

Output:

ProductColor	ProductColor	OrderQty	Sales
Bikes	Black	15050	19529109
Bikes	Silver	13271	17093187
Bikes	Total	28321	36622296
Clothing	Blue	8456	8425868
Clothing	Yellow	6295	6119206
Clothing	Total	14751	14545074
Components	Black	17346	13706122
Components	Red	18088	19820314
Components	Yellow	11762	10452007
Components	Total	47196	43978443
Total	Total	90268	95145813

Posted in: Using GROUPING and the WITH ROLLUP operator to create sub totals