SQL ROLLUP

Summary: In this tutorial, you’ll learn how to use the SQL ROLLUP to return aggregated results at multiple levels of detail.

Introduction to the SQL ROLLUP #

The GROUP BY clause allows you to group rows into groups by values in one or more columns. Typically, you use an aggregate function to return the aggregated result for each group.

However, the GROUP BY clause within an aggregate function can only return an aggregate result at a single level of detail.

To return aggregated results at multiple levels of details, you use the ROLLUP with the GROUP BY clause:

SELECT
  column1,
  column2,
  aggregate_function (column3)
FROM
  table_name
GROUP BY
  ROLLUP (column1, column2);Code language: SQL (Structured Query Language) (sql)

In this syntax, you place the columns you want to group within parentheses after the ROLLUP keyword.

The ROLLUP assumes that there is a hierarchy between column1 and column2:

column1 > column2Code language: SQL (Structured Query Language) (sql)

Therefore, the ROLLUP helps generate reports that include subtotals and totals.

How the ROLLUP works

• First, start with the most detailed grouping specified in the GROUP BY clause.
• Second, move up the hierarchy by removing columns and calculating subtotals.
• Third, end with a total row for all the rows.

SQL ROLLUP example #

We’ll use the salary_reports table to illustrate how the ROLLUP works:

Using ROLLUP with one column #

The following example uses the ROLLUP to calculate the total salary for each state and all states:

SELECT
  state,
  SUM(salary) total_salary
FROM
  salary_reports
GROUP BY
  ROLLUP (state)
ORDER BY
  state NULLS LAST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

   state    | total_salary
------------+--------------
 California |    280000.00
 Texas      |    180000.00
 NULL       |    460000.00Code language: SQL (Structured Query Language) (sql)

The output includes the total salary for each state, California and Texas, and the total salary for all states.

The NULL indicates the total row for all states. If you want to use a more meaningful label, you can use the COALESCE function:

SELECT
  COALESCE(state, 'Total') state,
  SUM(salary) total_salary
FROM
  salary_reports
GROUP BY
  ROLLUP (state)
ORDER BY
  state NULLS LAST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

   state    | total_salary
------------+--------------
 California |    280000.00
 Texas      |    180000.00
 Total      |    460000.00Code language: SQL (Structured Query Language) (sql)

Using ROLLUP with multiple columns #

The following statement uses the ROLLUP to calculate the subtotal salary for each state and job and also the grand total:

SELECT
  state,
  job,
  SUM(salary) total_salary
FROM
  salary_reports
GROUP BY
  ROLLUP (state, job)
ORDER BY
  state NULLS LAST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

   state    |    job    | total_salary
------------+-----------+--------------
 California | IT        |    150000.00
 California | Marketing |    130000.00
 California | NULL      |    280000.00
 Texas      | IT        |    100000.00
 Texas      | Marketing |     80000.00
 Texas      | NULL      |    180000.00
 NULL       | NULL      |    460000.00Code language: SQL (Structured Query Language) (sql)

Like the previous example, you can change the NULLs to more meaningful labels:

SELECT
  COALESCE(state, '') state,
  COALESCE(job, '') job,
  SUM(salary) total_salary
FROM
  salary_reports
GROUP BY
  ROLLUP (state, job)
ORDER BY
  state DESC,
  job DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

   state    |    job    | total_salary
------------+-----------+--------------
 Texas      | Marketing |     80000.00
 Texas      | IT        |    100000.00
 Texas      |           |    180000.00
 California | Marketing |    130000.00
 California | IT        |    150000.00
 California |           |    280000.00
            |           |    460000.00Code language: SQL (Structured Query Language) (sql)

Summary #

• Use the SQL ROLLUP to return aggregated results at multiple levels of detail.

Quiz #

Databases #

• PostgreSQL ROLLUP
• Oracle ROLLUP
• SQL Server GROUPING SETS