ROLLUP and CUBE with PostgreSQL

ROLLUP and CUBE are extensions to GROUP BY clause to include extra subtotal rows (commonly called super-aggregate rows) with the grant total row.

Rollup

ROLLUP generates subtotal rows for a hierarchy of values in the grouping columns. The easiest way to present it is on the example:

SELECT location, SUM(quantity) FROM sales
GROUP BY ROLLUP(location);

Multiple subtotals can be used with groups using more than 1 column:

SELECT location, product, SUM(quantity) FROM sales
GROUP BY ROLLUP(location, product)
ORDER BY location, product;

COALESCE might be handy to get rid of those ambiguous NULL values:

SELECT
  COALESCE(location, 'All locations') AS location,
  COALESCE(product, 'All products') AS product,
  SUM(quantity)
FROM sales
GROUP BY ROLLUP(location, product)
ORDER BY location, product;

Cube

CUBE generates subtotal rows for all combinations of values in the grouping columns. Let’s take a look at another example:

SELECT
  COALESCE(location, 'All locations') AS location,
  COALESCE(product, 'All products') AS product,
  SUM(quantity)
FROM sales
GROUP BY CUBE(location, product)
ORDER BY location, product;

Full examples can be found on dbfiddle.