Introducing grouping sets

Every advanced user of SQL should be familiar with GROUP BY and HAVING clauses. But are they also aware of CUBE, ROLLUP, and GROUPING SETS? If not, this chapter is a must-read.

Loading some sample data

To make this chapter a pleasant experience for you, we will compile some sample data that has been taken from the BP energy report, which can be found at 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...