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:
Last Updated :
26 Feb, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...