PostgreSQL – GROUPING SETS
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:
Last Updated :
07 Oct, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...