PostgreSQL - CUBE

The CUBE extension of the GROUP BY clause is invaluable for multi-dimensional aggregation. This feature allows analysts and developers to easily perform in-depth analyses of data from multiple perspectives.

Let us get a better understanding of the CUBE in PostgreSQL from this article.

What is CUBE in PostgreSQL?

CUBE is a part of the GROUP BY clause used in SQL queries to generate multiple grouping sets simultaneously. This is particularly useful in scenarios where one needs to analyze the data across several dimensions without running multiple queries.

Syntax

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

Parameters

Let's analyze the above syntax:

• CUBE subclause: Included in the GROUP BY clause of the SELECT statement to specify the dimensions for aggregation.
• Select list: Specifies the columns for analysis and includes aggregation functions.
• GROUP BY with CUBE: Defines the columns that form the basis for multiple grouping sets.

Return Value

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.

PostgreSQL CUBE 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: Full CUBE

The following query uses the CUBE subclause to generate multiple grouping sets as follows.

Query:

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

Output:👁 PostgreSQL CUBE Example

Explanation: This query generates all possible combinations of groupings based on 'course_name' and segment, including totals for each course, each segment, and a grand total.

Example 2: Partial CUBE

The following query performs a partial CUBE as follows.

Query:

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

Output:👁 PostgreSQL CUBE Example

Explanation: This query demonstrates grouping by course_name while using CUBE for just the segment, allowing for segment-wise and overall totals per course.