SQL OLAP

DB2 SQL OLAP : ROLLUP and CUBE


ROLLUP and CUBE both are extensions for “GROUP BY”clause.


We have a “Census” table to keep record of population by COUNTRY, CITY, ZIP and POPULATION (in millions) like this

Country City Zip Population
INDIA DELHI 110001 10
USA MIAMI 33160 15
USA DUBLIN 43016 2
FRANCE PARIS 75008 20
USA DUBLIN 43065 6
USA MIAMI 33017 12
INDIA DELHI 110029 4
INDIA MUMBAI 400065 30
USA NEW YORK 10001 18

 

Task 1: Get a report for total population by each COUNTRY and CITY.

SELECT COUNTRY,CITY,SUM(POPULATION) as POPULATION from Census 
GROUP BY  COUNTRY,CITY

A simple “GROUP BY” with SUM() will work perfectly.

Country City Population
USA NEW YORK 18
USA DUBLIN 8
FRANCE PARIS 20
USA MIAMI 27
INDIA DELHI 14
INDIA MUMBAI 30

 

Task 2: In last report add a new row for sub-total by country and then a new row for total population.

  1. This may be possible use some very complex SQL query by not by Simple “GROUP BY”

1. ROLLUP

  1. With ROLLUP booster “GROUP BY” will be able to create sub-total and final total with a very minor change in previous SQL query.
select COUNTRY,CITY,SUM(POPULATION) as total from Census 
GROUP BY ROLLUP (COUNTRY,CITY)

These are the Steps this query will follow

  1. Aggregate (in this case using SUM() function) by
    1. COUNTRY and CITY
    2. COUNTRY and NULL [ kind of SUB TOTAL]
    3. NULL and NULL          [ kind of FINAL TOTAL]
Country City Population
FRANCE PARIS 20  
FRANCE 20  <- Sub-total
INDIA DELHI 14  
INDIA MUMBAI 30  
INDIA 44  <- Sub-total
USA DUBLIN 8  
USA MIAMI 27  
USA NEW YORK 18  
USA 53  <- Sub-total
117  <- Final Total

 

If you want all sub-total and Final total at bottom of report add an “ORDER BY” clause to query.

select COUNTRY,CITY,SUM(POPULATION) as total from Census 
GROUP BY ROLLUP (COUNTRY,CITY) ORDER BY CITY
  1. ORDER BY CITY will push the NULL CITY to bottom of the result.
Country City Population
INDIA DELHI 14
USA DUBLIN 8
USA MIAMI 27
INDIA MUMBAI 30
USA NEW YORK 18
FRANCE PARIS 20
FRANCE 20  <- Sub-total
INDIA 44  <- Sub-total
USA 53  <- Sub-total
117  <- Final Total

1. CUBE

  1. Same as ROLLUP , CUBE is also an extension to “GROUP BY” clause.
  2. Consider CUBE as like : CUBE = ROLLUP + MORE LEVEL OF TOTAL
select COUNTRY,CITY,SUM(POPULATION) as total from Census 
GROUP BY CUBE (COUNTRY,CITY)

Aggregate (in this case using SUM() function) by [1,2,3 are same as ROLLUP]

  1. COUNTRY and CITY
  2. COUNTRY and NULL [ kind of SUB TOTAL]
  3. NULL and NULL          [ kind of FINAL TOTAL]
  4. NULL and CITY            [ New to CUBE]
Country City Population same as ROLLUP
FRANCE PARIS 20
FRANCE 20
INDIA DELHI 14
INDIA MUMBAI 30
INDIA 44
USA DUBLIN 8
USA MIAMI 27
USA NEW YORK 18
USA 53
117
DUBLIN 8 new level added by CUBE
MIAMI 27
MUMBAI 30
PARIS 20
NEW YORK 18
DELHI 14

IBM i developer.

View Comments
There are currently no comments.