SQL OLAP

DB2 SQL OLAP : GROUPING SETS


With “GROUP BY” clause SQL accept a single set of columns and based on that set final result is produced.

Something like this

SELECT country,city,sum(population) from Census GROUP By COUNTRY, CITY

“GROUP By COUNTRY, CITY” define a set with values COUNTRY and CITY. So only one list can be specified for a query.

If you need to you more then one set of values like you need to total population by COUNTRY/CITY and CITY/ZIP in same query. Standard “GROUP BY” clause does not give option to use multiple sets of columns.

GROUPING SETS

  1. This is anther extension to “GROUP BY” clause,
  2. This allows to use multiple column sets with “GROUP BY” clause within a single SQL Query,
SELECT country,city,zip,sum(population) from Census 
GROUP By GROUPING SETS((COUNTRY,CITY),(CITY,ZIP))
  1. This is kind of running
SELECT country,city,zip,sum(population) from Census GROUP By  COUNTRY,CITY

-AND-

SELECT country,city,zip,sum(population) from Census GROUP By CITY,ZIP

and then union the result.

Country City Zip Population
USA NEW YORK 18  SELECT country,city,zip, sum(population) from Census GROUP By COUNTRY,CITY
USA DUBLIN 8
FRANCE PARIS 20
USA MIAMI 27
INDIA DELHI 14
INDIA MUMBAI 30
PARIS 75008 20  SELECT country,city,zip,sum(population) from Census GROUP By CITY,ZIP
MIAMI 33160 15
MUMBAI 400065 30
DELHI 110029 4
DUBLIN 43065 6
MIAMI 33017 12
DUBLIN 43016 2
DELHI 110001 10
NEW YORK 10001 18

 

GROUPING SETS with ROLLUP and CUBE.

You can combine GROUPING SETS with ROLLUP and CUBE to create more complex combinations.

SELECT country,city,zip,sum(population) from Census 
GROUP By GROUPING SETS((COUNTRY,CITY), ROLLUP(CITY,ZIP))

 

Country City Zip Population
USA NEW YORK 18  Group by 1st set

(COUNTRY,CITY)

USA DUBLIN 8
FRANCE PARIS 20
USA MIAMI 27
INDIA DELHI 14
INDIA MUMBAI 30
DELHI 110001 10  Group by and  ROLLUP (CITY,ZIP)
DELHI 110029 4
DELHI 14  <- subtotal
DUBLIN 43016 2
DUBLIN 43065 6
DUBLIN 8   <- subtotal
MIAMI 33017 12
MIAMI 33160 15
MIAMI 27   <- subtotal
MUMBAI 400065 30
MUMBAI 30   <- subtotal
NEW YORK 10001 18
NEW YORK 18   <- subtotal
PARIS 75008 20
PARIS 20   <- subtotal
117  <- Final Total

IBM i developer.

View Comments
There are currently no comments.