Mastering PostgreSQL 12 - Third Edition

Applying grouping sets

The GROUP BY clause will turn many rows into one row per group. However, if you do reporting in real life, they might also be interested in the overall average. One additional line might be needed.

Here is how this can be achieved:

test=# SELECT region, avg(production)    FROM t_oil    GROUP BY ROLLUP (region); 
    region      |          avg           
----------------+----------------------- 
 Middle East    | 1992.6036866359447005  North America  | 4541.3623188405797101                 | 2607.5139860139860140 
(3 rows) 

ROLLUP will inject an additional line, which will contain the overall average. If you do reporting, it is highly likely that a summary line will be needed. Instead of running two queries, PostgreSQL can provide the data by running just a single query. ...