Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 90 ยท Querying with PostgreSQL Features

FILTER, GROUPING SETS, CUBE and ROLLUP

Count and sum conditionally with the aggregate FILTER clause, then build subtotals and cross-tabs in one query with GROUPING SETS, ROLLUP, CUBE and GROUPING().

Reports rarely need just one grouping. A sales summary wants revenue per genre, per month, per genre and month, and a grand total, and it often wants a few conditional numbers beside them: how many orders had more than one copy, how much was sold since April. You could write one query per number and glue them together, but PostgreSQL gives you two features that do it in a single pass over the data.

The aggregate FILTER clause restricts which rows an individual aggregate sees. GROUPING SETS, ROLLUP and CUBE compute several GROUP BY levels at once. FILTER is standard SQL that SQLite also supports, so those examples run live. CUBE and ROLLUP syntax is not available in sql.js, so those are read-only blocks, with a live UNION ALL version that produces the same rows so you can see what they do.

What runs live. The <TryIt> blocks use sql.js (SQLite) with the bookstore dataset. FILTER works there; GROUPING SETS, ROLLUP, CUBE and GROUPING() are PostgreSQL blocks that are not executed on this page.

Aggregate FILTER

Normally an aggregate looks at every row in its group. Adding FILTER (WHERE condition) right after the aggregate makes it ignore rows where the condition is not true. You can use a different filter on every aggregate in the same query:

SQL Several conditional aggregates in one pass ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

There are 10 orders, 3 of them with more than one copy. Fiction brought in 72.98, the average price across the Science order rows is 22.65, and 7 copies were sold before April. All five numbers come from a single scan of the joined rows.

Before FILTER, people wrote SUM(CASE WHEN genre = 'Fiction' THEN quantity * price END). Both give the same answer, but FILTER is shorter and clearer, and it avoids a classic trap: COUNT(CASE WHEN cond THEN 1 END) only works because COUNT skips NULL, while COUNT(CASE WHEN cond THEN 1 ELSE 0 END) silently counts every row. COUNT(*) FILTER (WHERE cond) cannot be misread.

Details worth knowing:

  • Rows removed by FILTER are ignored by that aggregate only. Other aggregates in the same query still see them.
  • An aggregate whose filter matches no rows returns NULL (or 0 for COUNT), as with any empty group. Wrap it in COALESCE(..., 0) when you need a number.
  • FILTER works with DISTINCT inside the aggregate, for example COUNT(DISTINCT customer_id) FILTER (WHERE status = 'paid').
  • In PostgreSQL it also works on window aggregates, SUM(x) FILTER (WHERE ...) OVER (...).
  • FILTER is different from WHERE: WHERE removes rows for the entire query, FILTER only for one aggregate.

A pivot table with FILTER

Putting one FILTER aggregate per category side by side turns rows into columns, a simple cross-tab:

SQL Copies per customer and genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Sofia Rossi bought 3 Fiction copies, Omar Haddad 2 Science copies, and Leo Martins 1 Mystery. Empty cells are NULL (no matching rows), except the Mystery column where COALESCE turned them into 0. The columns are fixed in the query: if a new genre appears, you add another FILTER line. For fully dynamic pivots PostgreSQL has the crosstab function in the tablefunc extension.

GROUPING SETS

A plain GROUP BY genre, month gives one level of detail. To also get totals per genre and a grand total, you would normally run three queries and UNION ALL them. GROUPING SETS lists the groupings you want, and PostgreSQL computes all of them in one query:

SQL
-- PostgreSQL (read-only)
SELECT b.genre, substr(o.ordered_on, 1, 7) AS month, SUM(o.quantity) AS copies
FROM   orders o
JOIN   books  b ON b.id = o.book_id
GROUP  BY GROUPING SETS ((b.genre, substr(o.ordered_on, 1, 7)), (b.genre), ());

Each parenthesized list is one grouping. (genre, month) is the detail level, (genre) is a subtotal per genre, and the empty () is the grand total over all rows. In the subtotal rows the columns that were not grouped are NULL.

Three shortcuts for common sets

ROLLUP(a, b) Sets (a,b), (a) and (). Subtotals up a hierarchy.
CUBE(a, b) Every combination: (a,b), (a), (b), ().
GROUPING SETS You list exactly the sets you want.

ROLLUP and CUBE are just shorthand for GROUPING SETS.

1 / 2

ROLLUP and CUBE

ROLLUP (a, b, c) is shorthand for the sets (a,b,c), (a,b), (a) and (): it drops columns from the right, so each level subtotals the one below. It suits natural hierarchies such as year, month, day or country, city.

CUBE (a, b) produces every combination of the columns: (a,b), (a), (b) and (). It suits cross-tab reports where each dimension may be totalled on its own.

SQL
-- PostgreSQL (read-only)
SELECT b.genre, substr(o.ordered_on, 1, 7) AS month, SUM(o.quantity) AS copies
FROM   orders o
JOIN   books  b ON b.id = o.book_id
WHERE  o.ordered_on < '2024-04-01'
GROUP  BY ROLLUP (b.genre, substr(o.ordered_on, 1, 7))
ORDER  BY b.genre, month;

For the first quarter of the bookstore data, this returns the detail rows plus the subtotals and total:

TEXT
 genre    | month   | copies
----------+---------+--------
 Children | 2024-03 |      3
 Children |         |      3
 Fiction  | 2024-02 |      1
 Fiction  |         |      1
 Poetry   | 2024-02 |      2
 Poetry   |         |      2
 Science  | 2024-03 |      1
 Science  |         |      1
          |         |      7

(Every genre had only one month in this period, so each subtotal equals its detail row. With CUBE (genre, month) you would get additional rows per month: 2024-02 with 3 copies and 2024-03 with 4.) If the two columns are independent of each other, you can mix plain columns with the shortcuts: GROUP BY genre, ROLLUP (month) always groups by genre and rolls up only the month.

You do not need PostgreSQL to see what ROLLUP does. It is a UNION ALL of the three groupings, and this query builds exactly that table with portable SQL. The two extra columns mark which columns were aggregated away; the next section explains why you need that:

SQL What ROLLUP produces, written as UNION ALL ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The result has nine rows, the same as the PostgreSQL output above. The difference is cost: this version scans the data three times, while ROLLUP does it in one pass.

The GROUPING() function

Subtotal rows use NULL for the columns that were grouped away, but real data can contain NULL too. If some orders had no genre, a row with genre IS NULL could be either "orders with an unknown genre" or "the grand total". GROUPING(column) removes the doubt: it returns 1 when the column was aggregated away in that row and 0 when it is a real grouping value.

SQL
-- PostgreSQL (read-only)
SELECT CASE WHEN GROUPING(b.genre) = 1 THEN 'ALL GENRES' ELSE b.genre END AS genre,
       CASE WHEN GROUPING(substr(o.ordered_on, 1, 7)) = 1 THEN 'ALL MONTHS'
            ELSE substr(o.ordered_on, 1, 7) END AS month,
       SUM(o.quantity) AS copies
FROM   orders o
JOIN   books  b ON b.id = o.book_id
GROUP  BY ROLLUP (b.genre, substr(o.ordered_on, 1, 7))
ORDER  BY GROUPING(b.genre), b.genre, GROUPING(substr(o.ordered_on, 1, 7)), month;

GROUPING accepts several arguments and returns a bit mask: GROUPING(a, b) is 2 when only a was aggregated away, 1 when only b, and 3 when both. Use it for labels like the ones above, to filter (HAVING GROUPING(genre) = 0 keeps rows that still have a genre) and to sort subtotals after their detail rows, as in the ORDER BY.

Practical advice

  • Use FILTER instead of CASE inside aggregates; it reads like the sentence you mean.
  • Reach for ROLLUP for hierarchies and CUBE for cross-tabs. GROUPING SETS is the general form when you want an unusual combination.
  • CUBE doubles the work with every column, so keep it to two or three.
  • Always sort explicitly. The order in which grouping sets come out is not guaranteed.
  • If a report grows to dozens of grouping levels, consider precomputing it into a materialized view.
  • For the portable UNION ALL technique and subtotal basics, see ROLLUP and Subtotals, and for the grouping basics GROUP BY.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SUM(amount) FILTER (WHERE status = 'paid') do?

    FILTER limits which rows one aggregate sees. Other aggregates in the same query, and the rest of the query, still see every row.
  2. 2. Which grouping sets does ROLLUP (a, b) produce?

    ROLLUP removes columns from the right, so you get the detail level, a subtotal per a, and the grand total. CUBE would add (b).
  3. 3. A subtotal row shows NULL in the genre column. How can you tell it from a real row whose genre is NULL?

    GROUPING(column) returns 1 when the column was aggregated away in that row and 0 when it holds a real group value.
  4. 4. How many grouping levels does CUBE (a, b, c) compute?

    CUBE builds every combination of the columns, which is 2 to the power of the number of columns. Three columns give 8 sets.

Summary

  • aggregate FILTER (WHERE cond) makes one aggregate ignore rows; it replaces CASE inside SUM and COUNT and builds simple pivots.
  • GROUPING SETS ((...), (...), ()) computes several GROUP BY levels in one scan.
  • ROLLUP (a, b) = (a,b), (a), (); CUBE (a, b) = all combinations, doubling per column.
  • Columns that were grouped away show NULL; GROUPING(col) tells whether a NULL is a subtotal marker (1) or real data (0).
  • Sort explicitly, and use GROUPING() in ORDER BY to place subtotals after their detail rows.
  • A portable UNION ALL produces the same rows as ROLLUP, at the cost of scanning the data once per level.

Next: LATERAL Joins.