Mastering PostgreSQL 9.6

Applying grouping sets

The GROUP BY clause will turn many rows into one row per group. However, if you do reporting in real life, you 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 running just a single query.

Of course, ...