The PostgreSQL ROLLUP belongs to the GROUP BY clause that provides a short cut for defining multiple grouping sets. Multiple columns grouped together forms a group set.
Unlike the CUBE subclause, ROLLUP does not yield all possible grouping sets based on the specified columns. It just makes a subset of those. The ROLLUP presupposes a hierarchy between the input columns and yields all grouping sets that make sense only if the hierarchy is considered. That’s why ROLLUP is usually used to generate the subtotals and the grand total for reports.
Syntax:
SELECT column1, column2, column3, aggregate(column4) FROM table_name GROUP BY ROLLUP (column1, column2, column3);
To better understand the concept let’s create a new table and proceed to the examples.
To create a sample table use the below command:
CREATE TABLE geeksforgeeks_courses( course_name VARCHAR NOT NULL, segment VARCHAR NOT NULL, quantity INT NOT NULL, PRIMARY KEY (course_name, segment) );
Now insert some data into it using the below command:
INSERT INTO geeksforgeeks_courses(course_name, segment, quantity) VALUES ('Data Structure in Python', 'Premium', 100), ('Algorithm Design in Python', 'Basic', 200), ('Data Structure in Java', 'Premium', 100), ('Algorithm Design in Java', 'Basic', 300);
Now that our table is set let’s look into examples.
Example 1:
The following query uses the ROLLUP subclause to find the number of products sold by course_name(subtotal) and by all course_name and segments (total) as follows:
SELECT course_name, segment, SUM (quantity) FROM geeksforgeeks_courses GROUP BY ROLLUP (course_name, segment) ORDER BY course_name, segment;
Output:
Example 2:
The following statement performs a partial ROLL UP as follows:
SELECT segment, course_name, SUM (quantity) FROM geeksforgeeks_courses GROUP BY segment, ROLLUP (course_name) ORDER BY segment, course_name;
Output: