PostgreSQL: Group By ROLLUP
In PostgreSQL, the ROLLUP clause is a subclause of the GROUP BY clause that is shorthand for defining multiple grouping sets. Unlike the CUBE clause, the ROLLUP does not generate all the possible grouping sets based on a specified list of columns, but just makes a subset of those.
The ROLLUP defines a hierarchy between the list of columns defined and generates all grouping sets based on that hierarchy. This is commonly used for the analysis of hierarchical data; e.g., total salary by department, gender, and company-wide total.
SELECT <column1>, <column2> FROM <table_name> GROUP BY ROLLUP(<column1>,<column2>...) [ORDER BY <column_list<];
For example, the
CUBE(c1,c2,c3) will generate eight possible combinations of grouping sets as shown below.
(c1,c2,c3) (c1,c2) (c1,c3) (c2,c3) (c1) (c2) (c3) ()
ROLLUP(c1,c2,c3) in GROUP BY will consider column hierarchy as
c1 -> c2 -> c3 and will generate four hierarchical grouping sets, as shown below.
(c1,c2,c3) (c1,c2) (c1) ()
Let’s understand how ROLLUP works using the following employee table.
The following query uses the ROLLUP clause to find the total salary of employees, the total salary of employees by
dept_id, and the total salary of employees (subtotal) by
SELECT dept_id, gender, SUM(salary) FROM employee GROUP BY ROLLUP(dept_id,gender) ORDER BY dept_id, gender;
As you can see above, the ROLLUP clause takes hierarchy from
dept_id -> gender and show grouping set for
(dept_id, gender), (dept_id), and
If you change the order of columns
dept_id in the select statement and ROLLUP, the result will be different.
SELECT gender, dept_id, SUM(salary) FROM employee GROUP BY ROLLUP(gender,dept_id) ORDER BY gender, dept_id;
As you can see above, now the driving column in the hierarchy is
gender. Hence it shows the result of all grouping sets
(gender, dept_id), (gender), and
It is also possible to do a partial rollup to reduce the number of subtotals generated.
SELECT dept_id, gender, SUM(salary) FROM employee GROUP BY dept_id, ROLLUP(gender) ORDER BY dept_id, gender;
The above query does partial rollup of grouping sets (
dept_id, gender) and (