Reading this following post is important in order to understand how ROLLUP works.
We saw detailed discussion in this blog post about GROUPING SETS in Postgres and how they improve the GROUP BY clause.
How To Use GROUPING SETS To Boost GROUP BY Queries In Postgres?
A shorthand notation is provided for specifying two common types of grouping set. A clause of the form CUBE and a clause of the form ROLLUP.
We saw how CUBE works in this post. Full And Partial CUBE In Postgres With Examples
Let's take a look at how ROLLUP works.
Postgres ROLLUP
ROLLUP is different from the CUBE. It does not create all possible grouping sets based on the columns we provide but it makes a subset of those columns.
The ROLLUP follows a hierarchical structure amongst the columns we provide and generates all grouping sets that indicate the hierarchy.
For e.g. ROLLUP(a, b, c) generates four grouping sets as follows:
(a, b, c)
(a, b)
(a)
()
It follows the a > b > c hierarchy.
ROLLUP ( a, (b, c), d )
is equivalent to
GROUPING SETS (
( a, b, c, d ),
( a, b, c ),
( a ),
( )
)
Syntax
SELECT
column_1,
column_2,
column_3,
aggregate (column_4)
FROM
table
GROUP BY
ROLLUP (column_1, column_2, column_3);
Also, you can partially perform the ROLLUP operation as well.
SELECT
column_1,
column_2,
column_3,
aggregate (column_4)
FROM
table
GROUP BY
ROLLUP (column_1, column_2);
Example
I will be using this database for all examples which is available on my Github public repo
SELECT
ship_city,
ship_country,
SUM (freight)
FROM
orders
GROUP BY
ROLLUP (ship_city, ship_country);
//Output
ship_city, ship_country, SUM
null null 64942.74
"Seattle" "USA" 1353.0599
"Kirkland" "USA" 70.009995
...
...
141 rows
Partial ROLLUP Example
SELECT
ship_city,
ship_country,
SUM (freight)
FROM
orders
GROUP BY
ship_city,
ROLLUP (ship_country);
//Output
ship_city, ship_country, SUM
"Seattle" "USA" 1353.0599
"Kirkland" "USA" 70.009995
"Paris" "France" 108.28
...
...
140 rows
ROLL UP is really helpful when we want to find the data per day, month, and year.
For example, this following query finds out the shipped freight per day, month and year to different locations.
SELECT
EXTRACT (YEAR FROM shipped_date) y,
EXTRACT (MONTH FROM shipped_date) m,
EXTRACT (DAY FROM shipped_date) d,
ship_city,
ship_country,
SUM (freight)
FROM
orders
GROUP BY
ship_city,
ship_country,
ROLLUP ( EXTRACT (YEAR FROM shipped_date),
EXTRACT (MONTH FROM shipped_date),
EXTRACT (DAY FROM shipped_date));
//Output
y m d ship_city ship_country SUM
1997 6 13 "Cunewalde" "Germany" 1007.64
1998 1 13 "Albuquerque" "USA" 37.52
1997 6 6 "Sevilla" "Spain" 4.32
Credit: PostgreSQL Docs
CUBE examples ROLL UP