Yogesh Chauhan's Blog

Full and Partial CUBE in Postgres with Examples

in Postgres on April 30, 2020

Reading this following post is important in order to understand how CUBE works.

We saw detailed discussion in this blog post about GROUPING SETS in Postgres and how they improve the GROUP BY clause.

How To Use GROUPING SETS To Boost GROUP BY Queries In Postgres?

A shorthand notation is provided for specifying two common types of grouping set. A clause of the form CUBE and a clause of the form ROLLUP.

Postgres CUBE

The CUBE allows you to generate multiple grouping sets.

Syntax


SELECT
    column_1,
    column_2,
    column_3,
    aggregate (column_4)
FROM
    table
GROUP BY
    CUBE (column_1, column_2, column_3);

The last CUBE part is equivalent to


GROUPING SETS (
    ( column_1, column_2, column_3),
    ( column_1, column_2    ),
    ( column_1, column_3 ),
    ( column_1),
    ( column_2, column_3 ),
    ( column_2    ),
    ( column_3 ),
    (         )
)

Let's say the number of columns specified in the CUBE is n then it will make 2n combinations.

Also, you can partially perform the CUBE operation as well.

Just like this


SELECT
    column_1,
    column_2,
    column_3,
    aggregate (column_4)
FROM
    table
GROUP BY
    CUBE (column_1, column_2);

Example

I will be using this database for all examples which is available on my Github public repo


SELECT
    ship_city,
    ship_country,
    SUM (freight)
FROM
    orders
GROUP BY
    CUBE (ship_city, ship_country);

//Output

ship_city,   ship_country,       SUM
null         null                64942.74
"Seattle"    "USA"               1353.0599
"Kirkland"   "USA"               70.009995
...
...
162 rows

Partial CUBE Example


SELECT
    ship_city,
    ship_country,
    SUM (freight)
FROM
    orders
GROUP BY
    ship_city,
    CUBE (ship_country);

//Output

ship_city,    ship_country,     SUM
"Seattle"     "USA"             1353.0599
"Kirkland"    "USA"             70.009995
"Paris"	      "France"	        108.28
...
...
140 rows

Credit: PostgreSQL Docs


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
General concepts in DrupalDrupalQuery to increment or decrement value in MySQL ignoring negative valuesSQL/MySQLObject destructuring in JavaScript: Unpacking fields from objects passed as function parameterJavaScriptWhat is IPS(Intrusion Prevention System), How Does It Work and What are the Detection Types?MiscellaneousHow to concatenate variable with string in Swift?SwiftWordPress: How to get ACF field values from another post?WordPress