Introducing grouping sets

Every advanced user of SQL should be familiar with GROUP BY and HAVING clauses. But are you also aware of CUBE, ROLLUP, and GROUPING SETS? If not, you might find this chapter worth reading.

Loading some sample data

To make this chapter a pleasant experience for you, I have compiled some sample data, which has been taken from the BP energy report: http://www.bp.com/en/global/corporate/energy-economics/statistical-review-of-world-energy/downloads.html.

Here is the data structure that will be used:

test=# CREATE TABLE t_oil ( 
  region      text, 
  country     text, 
  year int, 
  production  int, 
  consumption int 
); 
CREATE TABLE

The test data can be downloaded from our website using curl directly:

test=# COPY t_oil FROM PROGRAM ' 
  curl https://www.cybertec-postgresql.com/secret/oil_ext.txt ';
COPY 644

As in the previous chapter, you can download the file before importing it. On some operating systems, curl is not there by default or has not been installed, so downloading...