
jOOQ Masterclass
By :

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...