O'Reilly logo

Mastering PostgreSQL 10 by Hans-Jurgen Schonig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 by running just a single query. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required