Yogesh Chauhan's Blog

Full and Partial ROLLUP in Postgresql with Examples

in Postgres on May 1, 2020

Reading this following post is important in order to understand how ROLLUP 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.

We saw how CUBE works in this post. Full And Partial CUBE In Postgres With Examples

Let's take a look at how ROLLUP works.

Postgres ROLLUP

ROLLUP is different from the CUBE. It does not create all possible grouping sets based on the columns we provide but it makes a subset of those columns.

The ROLLUP follows a hierarchical structure amongst the columns we provide and generates all grouping sets that indicate the hierarchy.

For e.g. ROLLUP(a, b, c) generates four grouping sets as follows:


(a, b, c)
(a, b)
(a)
()

It follows the a > b > c hierarchy.


ROLLUP ( a, (b, c), d )

is equivalent to


GROUPING SETS (
    ( a, b, c, d ),
    ( a, b, c    ),
    ( a          ),
    (            )
)

Syntax


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

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


SELECT
    column_1,
    column_2,
    column_3,
    aggregate (column_4)
FROM
    table
GROUP BY
    ROLLUP (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
    ROLLUP (ship_city, ship_country);

//Output

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

Partial ROLLUP Example


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

//Output

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

ROLL UP is really helpful when we want to find the data per day, month, and year.

For example, this following query finds out the shipped freight per day, month and year to different locations.


SELECT
    EXTRACT (YEAR FROM shipped_date) y,
    EXTRACT (MONTH FROM shipped_date) m,
    EXTRACT (DAY FROM shipped_date) d,
    ship_city,
    ship_country,
    SUM (freight)
FROM
    orders
GROUP BY
    ship_city,
    ship_country,
    ROLLUP (   EXTRACT (YEAR FROM shipped_date),
    EXTRACT (MONTH FROM shipped_date),
    EXTRACT (DAY FROM shipped_date));

//Output
y     m  d   ship_city      ship_country  SUM
1997  6  13  "Cunewalde"    "Germany"     1007.64
1998  1  13  "Albuquerque"  "USA"         37.52
1997  6  6   "Sevilla"      "Spain"       4.32

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
Configuring Modules with @forward rule in SCSS (Sass)SCSSHow to get the first element with a class name xyz using JavaScript?JavaScriptHow to apply style only to first child and/or only to children other than the first child?CSSHow to create a placeholder loader (throbber) using CSS?CSSHow to check if radio button is checked or not using JavaScript?JavaScriptCreate a responsive pricing table using simple HTML and CSSCSS