PostgreSQL - GROUPING SETS
Last Updated : 01 Aug, 2024
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 essentially a set of columns by which you want to group your data. Typically, a single aggregate query defines a single grouping set, but with GROUPING SETS, you can define multiple grouping sets in a single query.
Syntax
SELECT column1, column2, aggregate_function(column3) FROM table_name GROUP BY GROUPING SETS ( (column1, column2), (column1), (column2), () );
PostgreSQL GROUPING SETS 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:
PostgreSQL CREATE TABLE geeksforgeeks_courses( course_name VARCHAR NOT NULL, segment VARCHAR NOT NULL, quantity INT NOT NULL, PRIMARY KEY (course_name, segment) ); 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: Grouping by Course Name and Segment
The following query defines a grouping set of the 'course_name' and 'segment'. It returns the number of products sold by brand and segment.
Query:
SELECT course_name, segment, SUM (quantity) FROM geeksforgeeks_courses GROUP BY course_name, segment;
Output:
Explanation: This query groups the data by both course_name and segment. For each combination of 'course_name' and 'segment', it calculates the total quantity of courses sold.
Example 2: Grouping by Course Name
The following query finds the number of courses sold by 'course_name'. It defines a grouping set of the 'course_name':.
Query:
SELECT course_name, SUM (quantity) FROM geeksforgeeks_courses GROUP BY course_name;
Output:
Explanation: This query groups the data by 'course_name' only. It calculates the total quantity of courses sold for each course name, regardless of the segment.
Example 3: Grouping by Segment
The following query finds the number of products sold by segment. It defines a grouping set of the segment.
Query:
SELECT segment, SUM (quantity) FROM geeksforgeeks_courses GROUP BY segment;
Output:
Explanation: This query groups the data by segment only. It calculates the total quantity of courses sold for each segment, regardless of the course name.
Example 4: Multiple Grouping Sets in a Single Query
In the following query, we will generate a single result set with the aggregates for all grouping sets.
Query:
SELECT GROUPING(course_name) grouping_course, GROUPING(segment) grouping_segment, course_name, segment, SUM (quantity) FROM geeksforgeeks_courses GROUP BY GROUPING SETS ( (course_name, segment), (course_name), (segment), () ) ORDER BY course_name, segment;
Output:
Explanation: This query uses the GROUPING function to distinguish between the different grouping sets, providing a more comprehensive analysis of the data.
Important Points About PostgreSQL GROUPING SETS
- The GROUPING function helps differentiate between aggregated and non-aggregated rows by returning 0 or 1, indicating whether a column is part of the current grouping set.
- An empty grouping set '()' can be used to calculate the grand total, aggregating all rows regardless of any grouping columns.
- GROUPING SETS can be combined with CUBE and ROLLUP to produce even more complex and comprehensive result sets.
- GROUPING SETS allow for complex aggregations that would otherwise require multiple UNION ALL operations.
Similar Reads
PostgreSQL - SELF JOIN In PostgreSQL, a SELF JOIN is a powerful technique that allows us to join a table with itself. This type of join is particularly useful for comparing rows within the same table, such as establishing hierarchical relationships or identifying duplicate records. Unlike other joins, there is no specific
4 min read
Group By Vs Distinct in PostgreSQL When working with PostgreSQL, efficiently organizing and retrieving data is critical for database performance and decision-making. Two commonly used clauses are DISTINCT and GROUP BY that serve distinct purposes in data retrieval. DISTINCT is used to filter out duplicate values, while GROUP BY is em
6 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 - STRING_AGG() Function The STRING_AGG() function in PostgreSQL is a powerful aggregate function used to concatenate a list of strings with a specified separator. This function is essential for combining string values from multiple rows into a single string, making data aggregation more efficient and readable. Let us get a
2 min read
PostgreSQL - GROUP BY clause The GROUP BY clause in PostgreSQL is an essential tool that allows us to group rows that share the same values in one or more columns. This powerful functionality is commonly used to perform aggregate calculations such as SUM(), COUNT(), AVG(), and more, enabling us to summarize data efficiently. In
4 min read