Mastering PostgreSQL 10

Combining grouping sets with the FILTER clause

In real-world applications, grouping sets can often be combined with FILTER clauses. The idea behind the FILTER clause is to be able to run partial aggregates.

Here is an example:

test=# SELECT region,   avg(production) AS all, 
  avg(production) FILTER (WHERE year  < 1990) AS old,  
  avg(production) FILTER (WHERE year  >= 1990) AS new  
FROM t_oil 
GROUP BY ROLLUP (region);     region     | all            | old            | new ---------------+----------------+----------------+----------------  Middle East   | 1992.603686635 | 1747.325892857 | 2254.233333333  North America | 4541.362318840 | 4471.653333333 | 4624.349206349                | 2607.513986013 | 2430.685618729 | 2801.183150183 
(3 rows) 

The idea here is that not all columns will use the ...