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
PostgreSQL - Managing Views Views in PostgreSQL are a powerful tool that allows you to simplify complex queries by abstracting them into virtual tables. They are derived from base tables or previously defined views, offering a convenient way to organize and manage your data without creating physical copies. In this article, we
4 min read
PostgreSQL - Joins The PostgreSQL JOIN statement is a powerful tool for combining data or rows from one or more tables based on a common field between them. These common fields are typically the primary key in the first table and the foreign key in the other table(s). By using different types of JOINs, we can perform
5 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
PostgreSQL EXPLAIN Statement When we work with large databases, query performance becomes critically important to ensure efficient data retrieval. One of the most effective tools to analyze and improve query performance in PostgreSQL is the EXPLAIN statement. EXPLAIN provides detailed information on how PostgreSQL executes quer
4 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 - SQL Optimization PostgreSQL is the most advanced general-purpose open source database in the world. pgAdmin is the most popular management tool or development platform for PostgreSQL. It is also an open source development platform. It can be used in any Operating Systems and can be run either as a desktop applicatio
5 min read
PostgreSQL - SOME Operator The PostgreSQL SOME operator is used to compare a scalar value with a set of values returned by a subquery. This operator is useful for performing conditional checks against multiple values, providing a flexible way to filter query results.Let us better understand the SOME Operator in PostgreSQL fro
3 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