Grouping sets

For those not familiar with grouping sets, let's briefly follow a scenario meant to quickly introduce and cover this notion. Consider the following screenshot:

Figure 13.24 – Two queries using a grouping set each

The groupBy(SALE.EMPLOYEE_NUMBER) construction from the left-hand side (respectively, groupBy(SALE.FISCAL_YEAR) from the right-hand side) is referred to as a grouping set. A grouping set can contain none (empty grouping set), one, or more columns. In our case, both grouping sets contain one column.

Getting a unified result set of these two result sets containing the aggregated data of both grouping sets can be done via the UNION ALL operator, as illustrated here:

Figure 13.25 – Union grouping sets

But, as you can see, even for only two grouping sets, this query is quite lengthy. Moreover, it needs to resolve two SELECT statements before combining their results into a single result set. Here...