PostgreSQL ROLLUP

This article describes how to use ROLLUP in a GROUP BY clause to generate a report on multiple dimensions in one query.

In PostgreSQL, the ROLLUP clause is a parameter of the GROUP BY clause that allows you to generate a report on multiple dimensions in one query. In some specific cases, the ROLLUP can simplify GROUPING SETS.

PostgreSQL ROLLUP syntax

The following syntax shows how to use ROLLUP in the GROUP BY clause:

SELECT ...
FROM table_name
[WHERE ...]
GROUP BY
  ROLLUP (
    group_expr_1
    [, group_expr_2, ...]
  )
;

Here, the ROLLUP clause is the parameter of the GROUP BY clause, it must follow the GROUP BY keyword.

A ROLLUP clause can be implemented by a GROUPING SETS clause, for example:

  • The ROLLUP(a, b) is equivalent to GROUPING SETS((a,b), (a), ()).
  • The ROLLUP(a, b, c) is equivalent to GROUPING SETS((a,b,c), (a,b), (a), ()).

ROLLUP Clauses are simpler and easier to read than GROUPING SETS clauses. However, its adaptability is not as good as GROUPING SETS.

PostgreSQL GROUP BY Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the Sakila sample database, the film table stores all films in the DVD store.

To find the number of films for each film rating and rental, and the number of films for each film rating, and the total number of all films, you can use the following statement with GROUPING SETS:

SELECT
  rating,
  rental_rate,
  count(*)
FROM
  film
GROUP BY
  GROUPING SETS (
    (rating, rental_rate),
    (rating),
    ()
  )
ORDER BY
  rating, rental_rate;

 rating | rental_rate | count
--------+-------------+-------
 G      |        0.99 |    64  ┐
 G      |        2.99 |    59  │───┐
 G      |        4.99 |    55  ┘   │───┐
 G      |             |   178  <───┘   │
 PG     |        0.99 |    62  ┐       │
 PG     |        2.99 |    64  │───┐   │
 PG     |        4.99 |    68  ┘   │───│
 PG     |             |   194  <───┘   │
 PG-13  |        0.99 |    72  ┐       │
 PG-13  |        2.99 |    74  │───┐   │
 PG-13  |        4.99 |    77  ┘   │───│
 PG-13  |             |   223  <───┘   │
 R      |        0.99 |    70  ┐       │
 R      |        2.99 |    60  │───┐   │
 R      |        4.99 |    65  ┘   │───│
 R      |             |   195  <───┘   │
 NC-17  |        0.99 |    73  ┐       │
 NC-17  |        2.99 |    66  │───┐   │
 NC-17  |        4.99 |    71  ┘   │───│
 NC-17  |             |   210  <───┘   │
        |             |  1000  <───────┘
(21 rows)

Here, we use 3 expressions in the GROUPING SETS clause, as follows:

  • (rating, rental_rate): Count the number of films for each film rating and rental.
  • (rating): Count the number for each film rating.
  • (): Count the number of all films.

In this case, the GROUPING SETS can be simplified by ROLLUP, as follows:

SELECT
  rating,
  rental_rate,
  count(*)
FROM
  film
GROUP BY
  ROLLUP (rating, rental_rate)
ORDER BY
  rating, rental_rate;

 rating | rental_rate | count
--------+-------------+-------
 G      |        0.99 |    64
 G      |        2.99 |    59
 G      |        4.99 |    55
 G      |             |   178
 PG     |        0.99 |    62
 PG     |        2.99 |    64
 PG     |        4.99 |    68
 PG     |             |   194
 PG-13  |        0.99 |    72
 PG-13  |        2.99 |    74
 PG-13  |        4.99 |    77
 PG-13  |             |   223
 R      |        0.99 |    70
 R      |        2.99 |    60
 R      |        4.99 |    65
 R      |             |   195
 NC-17  |        0.99 |    73
 NC-17  |        2.99 |    66
 NC-17  |        4.99 |    71
 NC-17  |             |   210
        |             |  1000
(21 rows)

In the above statement, we used ROLLUP (rating, rental_rate) instead GROUPING SETS ((rating, rental_rate), (rating), ()). This makes the statement simpler and more readable.

Conclusion

In PostgreSQL GROUP BY clauses, you can use ROLLUP to simplify GROUPING SETS in specific cases.