Open In App

PostgreSQL – GROUPING SETS

Improve
Improve
Like Article
Like
Save
Share
Report

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
Previous
Next
Share your thoughts in the comments
Similar Reads