Creating Totals and Subtotals in Postgres with ROLLUP and CUBE

Elizabeth Christensen uses a pair of analytical operators:

Postgres is being used more and more for analytical workloads. There’s a few hidden gems I recently ran across that are really handy for doing SQL for data analysis, ROLLUP and CUBE. Rollup and cube don’t get a lot of attention, but follow along with me in this post to see how they can save you a few steps and enhance your date binning and summary reporting.

I’ve used ROLLUP on occasion, but never found a great case when CUBE made sense in a report. I am, however, quite partial to GROUPING SETS, the third of these analytical operators and the one that gives you the most control.