PostgreSQL is used to structure the raw data in the form of tables which creates a database inside the DBMS to access it in the future. This data can be accessed using Structure Query Language or SQL queries for multiple purposes to find information. CUBE is used in the GROUP BY clause of the SQL query to get the aggregate of the column with other columns related to it.

This guide will explain how to use CUBE in PostgreSQL.

How to Use PostgreSQL CUBE

GROUP BY clause is used to get the aggregate of the columns by using different functions like sum(), avg(), etc. The GROUP BY clause doesn’t provide aggregation on multiple levels, so to solve this problem PostgreSQL uses CUBE. PostgreSQL CUBE is generally used for reporting purposes in SQL databases with some applications like PowerBi, Tableau, etc.

Syntax

The following is the syntax of the CUBE in PostgreSQL:

SELECT
  c1,
  c2,
  c3,
  aggregate (c4)
 FROM
  table_name
   GROUP BY
  CUBE (c1, c2, c3);

The above syntax suggests:

- It selects multiple columns from the table to perform some aggregation function on one of the columns with reference to the other column or columns in the table.
- After that, the resultant table is sorted for each of the columns that are mentioned in the GROUP BY clause using the CUBE clause.

Example 1: CUBE in PostgreSQL

Use the following query to access the sales table which uses the ORDER BY clause to return the table according to the list of columns provided under it:

SELECT * FROM sales
 ORDER BY continent, country, city;

Running the above query will display the data in the sales tables according to the continent, country, and city columns:

Use the following query to use CUBE in PostgreSQL under the GROUP BY clause:

SELECT continent, country, city, sum(units_sold)
 FROM sales
 GROUP BY CUBE   (continent, country, city);

The above query will display the sum of all the units_sold and then provide the sales for each continent, country, and city individually. It also displays the sum of units_sold in each continent and then units_sold in every country:

Example 2: Partial CUBE in PostgreSQL

The partial CUBE query is being used in the following code:

SELECT continent, country, city, sum(units_sold)
 FROM sales
   GROUP BY continent,
 CUBE (country, city);

The above code will display the units_sold in each country and city according to each continent from the sales table:

That’s all about using the PostgreSQL CUBE clause within the GROUP BY clause.

Conclusion

PostgreSQL database allows the use of the CUBE clause inside the GROUP BY clause to apply aggregation function on multilevel. GROUP BY clause does not apply aggregation functions like sum(), avg(), etc on multilevel columns to provide reports. Postgres uses the CUBE clause to work with multiple columns and can also apply it on a partial level by providing one column to order the result accordingly. This guide has demonstrated the process of using CUBE in the PostgreSQL database.