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 w3wiki_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 w3wiki_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 w3wiki_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 w3wiki_courses GROUP BY course_name, CUBE (segment) ORDER BY course_name, segment;
Output:
Contact Us