SQL Rollup

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Overview

ROLLUP in SQL is an extension of the GROUP BY clause, this SQL ROLLUP option allows the user to include extra rows that represent the subtotals, which are usually referred to as super-aggregate rows, along with a grand total row.

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.

    IDNameGenderSalaryCountry
    1AshMale5000USA
    2JamesMale4500India
    3NancyFemale5500USA
    4SaraFemale4000India
    5ThomasMale3500India
    6MarieFemale5000UK
    7BenjaminMale6500UK
    8ElizaFemale7000USA
    9GaryMale5500UK
    10CoreyMale5000USA

    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:

    CountryGenderTotalSalary
    IndiaFemale4000
    IndiaMale8000
    IndiaNULL12000
    UKFemale5000
    UKMale12000
    UKNULL17000
    USAFemale12500
    USAMale10000
    USANULL22500
    NULLNULL51500
  • 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:

    CountryGenderTotalSalary
    IndiaNULL12000
    UKNULL17000
    USANULL22500
    NULLNULL51500
  • 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:

    CountryGenderTotalSalary
    IndiaFemale4000
    IndiaMale8000
    IndiaNULL12000
    UKFemale5000
    UKMale12000
    UKNULL17000
    USAFemale12500
    USAMale10000
    USANULL22500

Conclusion

  • ROLLUP in SQL aggregation is used to perform multiple levels of analysis with a single query.
  • ROLLUP performs aggregations across multiple dimensions at different levels within a single SQL query as an extension to the GROUP BY clause.
  • SQL ROLLUP does not sort the results retrieved. An ORDER BY clause has to be used to sort the results.