{"id":2105,"date":"2025-02-14T21:24:58","date_gmt":"2025-02-14T14:24:58","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2105"},"modified":"2025-02-14T21:49:10","modified_gmt":"2025-02-14T14:49:10","slug":"postgresql-rollup","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-rollup\/","title":{"rendered":"PostgreSQL ROLLUP"},"content":{"rendered":"\r\n

Summary<\/strong>: In this tutorial, you’ll learn how to use the PostgreSQL ROLLUP<\/code> to create multiple levels of aggregation.<\/p>\r\n\r\n\r\n\r\n

PostgreSQL ROLLUP Overview #<\/a><\/h2>\r\n\r\n\r\n\r\n

In PostgreSQL, the GROUP BY<\/a><\/code> clause groups rows into multiple groups and you can apply an aggregate function<\/a> to each group. However, the GROUP BY<\/code> clause can only generate a single level of aggregation.<\/p>\r\n\r\n\r\n\r\n

To generate multiple levels of aggregation, you use the ROLLUP<\/code> option of the GROUP BY<\/code> clause.<\/p>\r\n\r\n\r\n\r\n

Here’s the syntax of the GROUP BY<\/code> with the ROLLUP<\/code> option:<\/p>\r\n\r\n\r\n

SELECT<\/span>\r\n  column1,\r\n  column2,\r\n  aggregate_function(column3)\r\nFROM<\/span>\r\n  table_name<\/span>\r\nGROUP<\/span> BY<\/span>\r\n  ROLLUP<\/span>(column1, column2);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this syntax, the ROLLUP(column1, column2)<\/code> generates the three groupings:<\/p>\r\n\r\n\r\n\r\n
    \r\n
  1. (column1, column2)<\/code>: Regular groupings that include aggregate data by values of both column1<\/code> and column2<\/code>.<\/li>\r\n\r\n\r\n\r\n
  2. (column1, NULL)<\/code>: Subtotal per column1<\/code>.<\/li>\r\n\r\n\r\n\r\n
  3. (NULL, NULL)<\/code>: Grand total.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
    The ROLLUP<\/code> assumes a hierarchical relationship between column1<\/code> and column2<\/code>:<\/p>\r\n\r\n\r\n
    column1 > column2<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    The ROLLUP<\/code> can help generate subtotal and grand total rows hierarchically.<\/p>\r\n\r\n\r\n\r\n
    PostgreSQL ROLLUP Examples #<\/a><\/h2>\r\n\r\n\r\n\r\n
    We’ll use the inventory_reports<\/code> table to demonstrate how the ROLLUP<\/code> works.<\/p>\r\n\r\n\r\n
    \r\nHere\u2019s the script for generating inventory_reports table:<\/summary>\r\n
    CREATE<\/span> TABLE<\/span> inventory_reports(\r\n     warehouse VARCHAR<\/span> NOT<\/span> NULL<\/span>,\r\n     brand VARCHAR<\/span> NOT<\/span> NULL<\/span>,\r\n     quantity INT<\/span> NOT<\/span> NULL<\/span>\r\n);\r\n\r\nINSERT<\/span> INTO<\/span> inventory_reports(warehouse, brand, quantity)\r\nVALUES<\/span>\r\n('San Jose'<\/span>, 'Apple'<\/span>, 100<\/span>),\r\n('San Francisco'<\/span>, 'Apple'<\/span>, 200<\/span>),\r\n('Texas'<\/span>, 'Apple'<\/span>, 300<\/span>),\r\n('San Jose'<\/span>, 'Samsung'<\/span>, 50<\/span>),\r\n('San Francisco'<\/span>, 'Samsung'<\/span>, 100<\/span>),\r\n('Texas'<\/span>, 'Samsung'<\/span>, 150<\/span>)\r\nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre><\/details>\r\n\r\n
       warehouse   |  brand  | quantity\r\n---------------+---------+----------\r\n San Jose      | Apple   |      100\r\n San Francisco | Apple   |      200\r\n Texas         | Apple   |      300\r\n San Jose      | Samsung |       50\r\n San Francisco | Samsung |      100\r\n Texas         | Samsung |      150<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    Using ROLLUP with one column #<\/a><\/h3>\r\n\r\n\r\n\r\n
    The following query uses the GROUP BY<\/code> clause to calculate the total quantity for each warehouse:<\/p>\r\n\r\n\r\n
    SELECT\r\n  warehouse,\r\n  SUM(quantity) AS<\/span> total_quantity\r\nFROM\r\n  inventory_reports\r\nGROUP BY\r\n  warehouse\r\nORDER BY\r\n  warehouse;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    Output:<\/p>\r\n\r\n\r\n
       warehouse   | total_quantity\r\n---------------+----------------\r\n San Francisco |            300\r\n San Jose      |            150\r\n Texas         |            450<\/code><\/span><\/pre>\r\n\r\n\r\n
    To include the grand total row, you can use the ROLLUP<\/code> as follows:<\/p>\r\n\r\n\r\n
    SELECT\r\n  warehouse,\r\n  SUM(quantity) AS<\/span> total_quantity\r\nFROM\r\n  inventory_reports\r\nGROUP BY\r\n  ROLLUP(warehouse)\r\nORDER BY\r\n  warehouse;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    Output:<\/p>\r\n\r\n\r\n
       warehouse   | total_quantity\r\n---------------+----------------\r\n San Francisco |            300<\/span>\r\n San Jose      |            150<\/span>\r\n Texas         |            450<\/span>\r\n NULL<\/span>          |            900<\/span>   -> grand total<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    In this example, the ROLLUP(warehouse)<\/code> adds one more row that includes the total quantity for all warehouses.<\/p>\r\n\r\n\r\n\r\n
    Using ROLLUP with multiple columns #<\/a><\/h3>\r\n\r\n\r\n\r\n
    The following query uses the GROUP BY<\/code> clause to generate total quantity by warehouse and brand:<\/p>\r\n\r\n\r\n
    SELECT<\/span>\r\n  warehouse,\r\n  brand,\r\n  SUM(quantity) AS<\/span> total_quantity\r\nFROM<\/span>\r\n  inventory_reports\r\nGROUP<\/span> BY<\/span>\r\n  warehouse,\r\n  brand\r\nORDER<\/span> BY<\/span>\r\n  warehouse,\r\n  brand;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    Output:<\/p>\r\n\r\n\r\n
       warehouse   |  brand  | total_quantity\r\n---------------+---------+----------------\r\n San Francisco | Apple   |            200\r\n San Francisco | Samsung |            100\r\n San Jose      | Apple   |            100\r\n San Jose      | Samsung |             50\r\n Texas         | Apple   |            300\r\n Texas         | Samsung |            150<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    The output does not show the subtotal by warehouse and total quantity by all warehouses.<\/p>\r\n\r\n\r\n\r\n
    To generate subtotal and grand total, you use the ROLLUP<\/code> option as follows:<\/p>\r\n\r\n\r\n
    SELECT<\/span>\r\n  warehouse,\r\n  brand,\r\n  SUM(quantity) AS<\/span> total_quantity\r\nFROM<\/span>\r\n  inventory_reports\r\nGROUP<\/span> BY<\/span>\r\n  ROLLUP<\/span>(warehouse, brand)\r\nORDER<\/span> BY<\/span>\r\n  warehouse NULLS LAST<\/span>,\r\n  brand NULLS LAST<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    Output:<\/p>\r\n\r\n\r\n
       warehouse   |  brand  | total_quantity\r\n---------------+---------+----------------\r\n San Francisco | Apple   |            200\r\n San Francisco | Samsung |            100\r\n San Francisco | NULL    |            300   --> subtotal\r\n San Jose      | Apple   |            100\r\n San Jose      | Samsung |             50\r\n San Jose      | NULL    |            150   --> subtotal\r\n Texas         | Apple   |            300\r\n Texas         | Samsung |            150\r\n Texas         | NULL    |            450   --> subtotal\r\n NULL          | NULL    |            900   --> grand total<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    In this example, the ROLLUP(warehouse, brand)<\/code>:<\/p>\r\n\r\n\r\n\r\n
    Try it<\/a><\/p>\r\n\r\n\r\n\r\n
    Try it<\/a><\/p>\r\n\r\n\r\n\r\n