A Multi-Level Sales Summary

A Multi-Level Sales Summary for BookNest

The pieces combine into BookNest's yearly finance report: sales per genre and year with subtotals, each genre's share of its year, the web share and returned lines, in one query and one scan:

438-summary.sql: ROLLUP, FILTER and a window over grouped rowsSQL

SELECT coalesce(yr::text, 'all') AS year,
       CASE WHEN GROUPING(genre) = 1 THEN '(total)' ELSE genre END AS genre,
       sum(gross_amount) AS gross,
       round(100 * sum(gross_amount)
             / sum(sum(gross_amount)) OVER (PARTITION BY yr, GROUPING(genre)), 1) AS pct_year,
       round(100 * sum(gross_amount) FILTER (WHERE channel = 'web')
             / sum(gross_amount), 1) AS web_pct,
       count(*) FILTER (WHERE status = 'returned') AS returns
FROM (SELECT *, extract(year FROM order_date)::int AS yr FROM mart.sales
      WHERE status <> 'cancelled') s
GROUP BY ROLLUP (yr, genre)
ORDER BY yr, GROUPING(genre), gross DESC;

Output

 year |      genre      |   gross    | pct_year | web_pct | returns
------+-----------------+------------+----------+---------+---------
 2025 | Technology      |  648748.00 |     29.2 |    20.0 |     618
 2025 | Cooking         |  496056.00 |     22.3 |    20.2 |     765
 ...
 2025 | (total)         | 2219580.47 |    100.0 |    20.3 |    3782
 2026 | Technology      |  285782.50 |     26.4 |    20.6 |     237
 ...
 2026 | Travel          |   90131.25 |      8.3 |    19.5 |     139
 2026 | (total)         | 1083846.83 |    100.0 |    20.4 |    1623
 all  | (total)         | 3303427.30 |    100.0 |    20.3 |    5405

Window functions run after grouping, so sum(sum(gross_amount)) OVER (...) adds up grouped rows; partitioning by GROUPING(genre) keeps genre rows apart from their subtotal, which gets 100. Views, Partitions, Parallelism materializes reports like this so dashboards stop recomputing them.

MENU