SQL Rollup

Introduction to the SQL ROLLUP

We can use the SQL ROLLUP option to generate multiple grouping sets using a single query. ROLLUP in SQL is a sub-clause of the GROUP BY clause, which provides a shorthand for defining multiple grouping sets. A grouping set is a set of columns that we can use to perform the GROUP BY operation.

When generating grouping sets, SQL ROLLUP assumes a hierarchy among the columns and only generates grouping sets based on this hierarchy. The ROLLUP in SQL is commonly used to calculate the aggregates of hierarchical data such as sales by year > quarter > month.

Levels of Aggregation

If n is the number of grouping columns, then ROLLUP creates n+1 levels of subtotals and grand total. Because ROLLUP removes the right-most column at each step, the column order has to be specified carefully.

For Example - ROLLUP(A, B, C) creates four groups assuming the hierarchy A > B > C, as follows:

ROLLUP removes the right-most column at each step. There are no groups for (A, C) and (B, C).

Syntax

The below given example illustrates the basic section of the SQL ROLLUP.

In the above syntax, the input column is (col1,col2), having the hierarchy col1 > col2. The SQL ROLLUP generates all grouping sets considering this hierarchy. This is the reason why we often use ROLLUP to generate the subtotals and the grand total for reporting purposes.

Parameters

group‑expression - The SQL ROLLUP clause takes any expression that is not an aggregate or a grouping function that includes constants and column references in FROM-specified tables as a parameter.

Restrictions

GROUP BY ROLLUP does not sort the results retrieved. To sort data, an ORDER BY clause must follow the GROUP BY clause.

Example

• Aggregating the Full Data Set

  Let us consider a Employees table having the salary of employees in a multinational company. The table contains ID, Name, Gender, Salary, and Country as the fields.

  ID	Name	Gender	Salary	Country
  1	Ash	Male	5000	USA
  2	James	Male	4500	India
  3	Nancy	Female	5500	USA
  4	Sara	Female	4000	India
  5	Thomas	Male	3500	India
  6	Marie	Female	5000	UK
  7	Benjamin	Male	6500	UK
  8	Eliza	Female	7000	USA
  9	Gary	Male	5500	UK
  10	Corey	Male	5000	USA

  Using the above GROUP BY query along with the ROLLUP option helps us to group salary by Country and Gender. ROLLUP in SQL also computes the subtotal at the Country level and Grand Total.

  Output:

  Country	Gender	TotalSalary
  India	Female	4000
  India	Male	8000
  India	NULL	12000
  UK	Female	5000
  UK	Male	12000
  UK	NULL	17000
  USA	Female	12500
  USA	Male	10000
  USA	NULL	22500
  NULL	NULL	51500

• Using SQL ROLLUP with the HAVING Clause

  Using the above example of the Employees table, we can learn how to use the HAVING clause with ROLLUP to restrict the GROUP BY results.

  The following query uses the GROUPING function, taking one of the GROUP BY expressions as an argument. For each row, GROUPING returns one of the following:

  • 0: The column is part of the group for that row
  • 1: The column is not part of the group for that row

  The following query produces only those ROLLUP categories where only the Gender column is sub-totaled, based on the expression in the GROUPING function.

  Output:

  Country	Gender	TotalSalary
  India	NULL	12000
  UK	NULL	17000
  USA	NULL	22500
  NULL	NULL	51500

• How to perform a partial SQL ROLLUP?

  We often use ROLLUP in SQL to perform a partial roll-up that reduces the number of subtotals calculated, as shown in the following example.

  Using the above Employees table, we can try doing partial ROLLUP by making a super-aggregate summary for the Gender column, not the Country column.

  Output:

  Country	Gender	TotalSalary
  India	Female	4000
  India	Male	8000
  India	NULL	12000
  UK	Female	5000
  UK	Male	12000
  UK	NULL	17000
  USA	Female	12500
  USA	Male	10000
  USA	NULL	22500