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.
Similar Reads
PostgreSQL - ROLLBACK
The PostgreSQL rollback command is a tool for maintaining data integrity within PostgreSQL databases. This command allows users to undo changes made during a transaction and ensuring that any errors or unexpected issues do not compromise the databases accuracy. By utilizing the rollback transaction
5 min read
PostgreSQL - SELECT
PostgreSQL SELECT statement is an command for retrieving data from tables within a PostgreSQL database. It enables users to specify which columns to fetch and apply filters using the WHERE clause for targeted results. In this article, We will learn about the PostgreSQL SELECT in detail by understand
3 min read
PostgreSQL - Subquery
Subqueries, also known as nested queries or inner queries, are queries embedded within another SQL query. They allow you to perform multiple queries within a single PostgreSQL command, making your SQL statements more powerful and efficient. They allow you to perform multiple queries within a single
3 min read
PostgreSQL - CASE
In PostgreSQL, the CASE expression allows you to perform conditional operations within your SQL queries. It evaluates a list of conditions and returns a result when the first condition is met. If no conditions are met, it returns the result specified in the ELSE clause. Let us better understand the
3 min read
PostgreSQL - CUBE
The CUBE extension of the GROUP BY clause is invaluable for multi-dimensional aggregation. This feature allows analysts and developers to easily perform in-depth analyses of data from multiple perspectives. Let us get a better understanding of the CUBE in PostgreSQL from this article. What is CUBE i
2 min read
Grouping Data with ROLLUP in PostgreSQL
In database management, reducing and compressing data is one of the most significant jobs. PostgreSQL, which is an open-source, stable relational database management system, boosts many features that are meant to help in this regard. Another element is ROLLUP which maintains the hierarchical data ag
4 min read
PostgreSQL - SUM() Function
The SUM() function in PostgreSQL is used to calculate the sum of values in a numeric column. This article will guide you through the syntax, important considerations, and practical examples of using the SUM() function in PostgreSQL. SyntaxSUM(column) The following points need to be kept in mind whil
2 min read
PostgreSQL - INSERT
PostgreSQL INSERT statement is one of the fundamental SQL commands used to add new rows to a specified table within a PostgreSQL database. This command allows users to insert data efficiently, whether for a single record or multiple records at once. With the PostgreSQL INSERT INTO clause, we can spe
5 min read
PostgreSQL - UNION operator
The PostgreSQL UNION operator is a powerful tool used to combine result sets from multiple queries into a single result set. It helps in consolidating data from different sources, making it easier to analyze and report. From this article, we can better understand the UNION Operator in PostgreSQL Syn
3 min read
PostgreSQL - GROUPING SETS
In PostgreSQL, the GROUPING SETS feature allows users to generate result sets that are equivalent to those produced by the UNION ALL of multiple GROUP BY clauses. This feature is highly useful for creating complex reports with multiple levels of aggregation in a single query. A grouping set is essen
3 min read