Every advanced user of SQL should be familiar with the GROUP BY and HAVING clauses. But are they also aware of CUBE, ROLLUP, and GROUPING SETS? If not, this chapter is a must-read. What is the basic idea behind a grouping set? Basically, the concept is simple: using a group set, you can combine various aggregations into a single query. The main advantage is that you have to read data only once while producing many different aggregation sets at once.
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...