📔
Databases
  • Table of contents
  • SQL
    • Getting Started
      • PgAdmin Tool
    • Data Types
      • Arrays
      • Date/Time/Stamps
      • JSON
      • Internal Functions
      • Sequences
      • User Defined Data Types
    • Database
      • Schema
    • Table
      • Aggregation
      • Usefull Functions
      • Combining Tables
      • Constraints
      • Common Table Expression
      • GROUP BY and HAVING
      • OPERATORS
      • ORDER BY and DISTINCT
      • Views
      • WHERE Clause
    • Order of SQL Execution
    • Advance Table
      • Internals
      • Managing Tables
      • Partitioning Tables
      • Pivotal or Crosstab Tables
    • Joins
      • Cross & Natural Joins
      • Full, Multiple & Self Joins
      • Inner Join
      • Left and Right JOIN
    • Functions
      • Cursors
      • PL/pgSQL
      • Stored Procedures
      • Triggers
        • More on Triggers
    • Indexing
      • Custom Indexes
      • GIN Index
      • Indexes
      • SQL
      • Unique Index
    • Summarization
      • SubQueries
      • Window
  • MongoDB
    • Mongo Administration
    • MongoDB Aggregation
    • MQL
  • Redis
  • Cassandra
    • CQL
    • Data Modelling
      • Advance Data Modelling
    • Cassandra Administration
    • Cassandra Features
Powered by GitBook
On this page
  • Rollup
  • More Queries

Was this helpful?

  1. SQL
  2. Summarization

Window

Rollup

The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row.

The ROLLUP assumes a hierarchy among the input columns. For example, if the input column is (c1,c2), the hierarchy c1 > c2.

-- SYNTAX
SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table
GROUP BY ROLLUP (c1, c2);

-- EXAMPLE

SELECT region, round(avg(imports), 2)
FROM trades
GROUP BY rollup (region);

-- OUTPUT

    region      |      round      
-----------------+-----------------
                |  73325290066.17
NORTH AMERICA   |  70993412846.58
SOUTH AMERICA   | 152968951703.49
ASIA            | 115476296703.29
CENTRAL AMERICA |  38853979545.30


SELECT region, country, round(avg(imports), 2)
FROM trades
WHERE country in ('USA', 'Argentina', 'Singapore', 'Brazil')
GROUP BY rollup (region, country)
order by 1;

    region     |  country  |      round       
---------------+-----------+------------------
 ASIA          | Singapore |  209191973057.39
 ASIA          |           |  209191973057.39
 SOUTH AMERICA | Argentina |   40764435340.44
 SOUTH AMERICA | Brazil    |  103647680768.84
 SOUTH AMERICA | USA       | 1590283700017.03
 SOUTH AMERICA |           |  579677530557.70
               |           |  482346579011.01


SELECT region, country, round(avg(imports / 1000000))
FROM trades
WHERE country in ('USA', 'France', 'Germany')
GROUP BY cube (region, country);

    region     | country |  round  
---------------+---------+---------
               |         |  974454
 EUROPE        | France  |  481421
 SOUTH AMERICA | USA     | 1590284
 EUROPE        | Germany |  800653
 SOUTH AMERICA |         | 1590284
 EUROPE        |         |  649743
               | USA     | 1590284
               | Germany |  800653
               | France  |  481421


SELECT region, country, round(avg(imports) / 1000000, 2)
FROM trades
WHERE country in ('USA', 'FRANCE', 'Germany')
GROUP BY
    grouping sets ( (), country, region );

    region     | country |   round    
---------------+---------+------------
               |         | 1195468.31
               | USA     | 1590283.70
               | Germany |  800652.92
 SOUTH AMERICA |         | 1590283.70
 EUROPE        |         |  800652.92



SELECT region, country, round(avg(imports) / 1000000, 2)
FROM trades
WHERE country in ('USA', 'FRANCE', 'Germany')
GROUP BY
    grouping sets ( (), country, region );

    region     | country |   round    
---------------+---------+------------
               |         | 1195468.31
               | USA     | 1590283.70
               | Germany |  800652.92
 SOUTH AMERICA |         | 1590283.70
 EUROPE        |         |  800652.92


SELECT region,
       avg(exports)                                     as avg_all,
       avg(exports) filter ( WHERE trades.year < 1995 ) as avg_old,
       avg(exports) filter ( WHERE trades.year >= 1995) as avg_latest
FROM trades
GROUP BY
    rollup (region);

     region      |        avg_all        |        avg_old        |      avg_latest       
-----------------+-----------------------+-----------------------+-----------------------
                 |  72443407670.13915858 |  42905138774.45808383 |  74914795899.38702405
 NORTH AMERICA   |  74417101760.04615385 |  62932794640.69230769 |  75693135884.41880342
 SOUTH AMERICA   | 109338636484.50140845 |  49195642528.37777778 | 118069071091.03548387
 ASIA            | 122562815407.87438424 |  59879433497.46250000 | 129413458239.61338798
 CENTRAL AMERICA |  34961597492.66203704 |  12904661532.05555556 |  36966773489.08080808


SELECT AVG(imports), avg(exports)
FROM trades;

         avg          |         avg          
----------------------+----------------------
 73325290066.16504854 | 72443407670.13915858

More Queries

SELECT country, year, imports, exports, avg(exports) OVER () as avg_exports
FROM trades;

SELECT country, year, imports, exports, avg(exports) OVER (partition by country) as avg_exports
FROM trades;

SELECT country, year, imports, exports, avg(exports) OVER (partition by year < 2000 ) as avg_exports
FROM trades;


SELECT country, year, exports, min(exports) OVER (PARTITION BY country order by year)
FROM trades
WHERE year > 2001
  and country in ('USA', 'France');
PreviousSubQueriesNextMongoDB

Last updated 3 years ago

Was this helpful?