The PostgreSQL ROLLUP clause is a powerful extension to the GROUP BY clause, providing a shortcut for defining multiple grouping sets. When multiple columns are grouped together, they form a grouping set. By organizing and aggregating data hierarchically, ROLLUP creates meaningful summaries without the need for complex subqueries. ROLLUP is particularly useful for generating subtotals and grand totals in reports.
In this article, we will explain the PostgreSQL ROLLUP syntax, explore its practical use cases, and provide multiple examples to demonstrate its functionality. We will also highlight the key differences between ROLLUP and CUBE in PostgreSQL, helping us choose the right operation based on our needs.
What is PostgreSQL ROLLUP?
ROLLUP in PostgreSQL is used to create subtotals and grand totals in the result set. It assumes a hierarchical relationship between the columns and generates only those grouping sets that make sense within that hierarchy. This is different from the CUBE subclause, which generates all possible combinations.
Syntax
SELECT
column1,
column2,
column3,
aggregate(column4)
FROM
table_name
GROUP BY
ROLLUP (column1, column2, column3);
Key Terms
column1, column2, column3: These are the columns that will be used for grouping.
aggregate(column4): This is the aggregate function we want to apply (e.g., SUM, COUNT, AVG).
PostgreSQL ROLLUP Examples
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. This will help us illustrate how ROLLUP works in various aggregation scenarios.
1. Creation of the Sample table
CREATE TABLE geeksforgeeks_courses(
course_name VARCHAR NOT NULL,
segment VARCHAR NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (course_name, segment)
);
2. Insertion of values
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);
Example 1: Generating Subtotals and Grand Totals Using ROLLUP
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.
Query:
SELECT
course_name,
segment,
SUM (quantity)
FROM
geeksforgeeks_courses
GROUP BY
ROLLUP (course_name, segment)
ORDER BY
course_name,
segment;
Output
Explanation:
This query calculates the sum of quantities for each course_name and segment, and also generates subtotals for each course_name and a grand total across all courses and segments.
Example 2: Partial ROLLUP for Grouping by Segment
In this example, we will modify the ROLLUP to generate subtotals within each segment and across all segments. This demonstrates how ROLLUP allows us to apply partial aggregation.
Query:
SELECT
segment,
course_name,
SUM (quantity)
FROM
geeksforgeeks_courses
GROUP BY
segment,
ROLLUP (course_name)
ORDER BY
segment,
course_name;
Output
Explanation:
This query first groups data by segment and then by course_name within each segment, generating subtotals for each course_name and a total for the entire segment.
Conclusion
The PostgreSQL ROLLUP clause is an important tool for generating hierarchical aggregations in our reports. By using ROLLUP, we can easily calculate subtotals and grand totals for grouped data, making it a must-know feature for any PostgreSQL user working with summary reports. Additionally, it simplifies complex queries by eliminating the need for manual aggregation, saving time and effort in data analysis.