I have seen some jobs that ask for experience with functions like ROLLUP and CUBE. Previously I had never heard of them. Seems like some sort of geometry. Ha. Well now I know better.

ROLLUP is a subclass of GROUP BY. It produces subaggregate rows, or aggregates of aggregates. It works well with the SUM aggregate function. But you can use it with any aggregate function. For each group set, you get a single line summary. If the output of a normal GROUP BY is called regular rows, then the extra rows from the ROLLUP are called superaggregate rows.

Next up we have the CUBE. This is like a 3D ROLLUP. As such it is a subclass of GROUP BY. You get all the normal output from ROLLUP. You also get subtotals for the groupings.

Something that helps with formatting ROLLUP and CUBE output is GROUPING. This is a function that returns either 0 or 1. If you do not use ROLLUP or CUBE, GROUPING returns a 0. If you are on a superaggregate row of output, GROUPING returns a 1. Otherwise it returns a 0. You can use the GROUPING in your SELECT to transform the output. For example, you can format some value selected. Or you could replace the text entirely.

The final topic of the day is GROUPING SETS. They work with GROUP BY. They control what groups to display in the output. You pass lists in the GROUPING SETS. A NULL in the list indicates you want a single grand total. The result is almost like separate SQL statements GROUP BY each list, and the whole thing UNION ALL together.