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 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 BYGROUPING 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: 

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
GROUPBY
 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:

SELECTGROUPING(course_name) grouping_course,
 GROUPING(segment) grouping_segment,
 course_name,
 segment,
 SUM (quantity)
FROM
 geeksforgeeks_courses
GROUP BYGROUPING 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.