PostgreSQL – CUBE

In PostgreSQL, CUBE is used to generate multiple grouping sets at once. It is a subclass of the GROUP BY clause and comes in handy while generating multiple grouping sets. A grouping set is a set of columns to which you want to group. Syntax:

SELECT
    column1,
    column2,
    column3,
    aggregate (column4)
FROM
    table_name
GROUP BY
    CUBE (column1, column2, column3);

Let’s analyze the above syntax:

• First, define the CUBE subclause in the GROUP BY clause of the SELECT statement.
• Then in the select list, indicate the columns which you want to analyze and add the aggregation function expressions.
• Lastly, in the GROUP BY clause, set the dimension columns within the parentheses of the CUBE subclause.

The query yield all feasible grouping sets based on the dimension columns set in CUBE. The CUBE subclause is a short way to define multiple grouping sets. Normally if the number of columns set in the CUBE is n, then it generates 2n combinations. 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 uses the CUBE subclause to generate multiple grouping sets as follows:

SELECT
    course_name,
    segment,
    SUM (quantity)
FROM
    geeksforgeeks_courses
GROUP BY
    CUBE (course_name, segment)
ORDER BY
    course_name,
    segment;

Output: Example 2: The following query performs a partial CUBE as follows:

SELECT
    course_name,
    segment,
    SUM (quantity)
FROM
    geeksforgeeks_courses
GROUP BY
    course_name,
    CUBE (segment)
ORDER BY
    course_name,
    segment;

Output: