# How to Use PostgreSQL CUBE

> The CUBE clause in PostgreSQL can be used under the GROUP BY clause to apply an aggregate function on multilevel columns to generate reports.

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-use-postgresql-cube/)