Deviopath › PostgreSQL

Sales Report With Subtotals

Hard · Grouping

GROUP BY ROLLUP (a, b) returns the normal groups, a subtotal per a, and a grand total. In the extra rows the rolled-up columns are NULL. GROUPING(col) returns 1 when col was rolled up in that row, so you can tell a subtotal NULL from a real NULL in the data.

Build a sales report from sales. Return region, category and total (the sum of amount):

- one row per region and category; a NULL category is shown as 'Uncategorized' - one subtotal row per region, with category 'All categories' - one grand total row, with region 'All regions' and category 'All categories'

Sort by region (grand total last), then by the category label alphabetically, with each region's subtotal after its categories.

Next: Customer JSON Documents