In PostgreSQL, the GROUPING SETS are used to generate a result set equivalent to which generated by the UNION ALL of the multiple GROUP BY clauses. A grouping set is a set of columns by which the user group. Typically, a single aggregate query defines a single grouping set.
Syntax for GROUPING SETS –
SELECT column1, column2, aggregate_function(column3) FROM table_name GROUP BY GROUPING SETS ( (column1, column2), (column1), (column2), () );
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:
CREATE TABLE geeksforgeeks_courses( course_name VARCHAR NOT NULL, segment VARCHAR NOT NULL, quantity INT NOT NULL, PRIMARY KEY (course_name, segment) );
Now insert some data into it using the below command:
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:
The following query defines a grouping set of the course_name and segment. It returns the number of products sold by brand and segment.
SELECT course_name, segment, SUM (quantity) FROM geeksforgeeks_courses GROUP BY course_name, segment;
Output:
Example 2:
The following query finds the number of courses sold by course_name. It defines a grouping set of the course_name:
SELECT course_name, SUM (quantity) FROM geeksforgeeks_courses GROUP BY course_name;
Output:
Example 3:
The following query finds the number of products sold by segment. It defines a grouping set of the segment:
SELECT segment, SUM (quantity) FROM geeksforgeeks_courses GROUP BY segment;
Output:
Example 4:
In the following query, we will generate a single result set with the aggregates for all grouping sets.
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: