Rollup and Cube Example

with t as (select 'California' state, 'Los Angeles' city, 'AA' categ, 1 nbr from dual
union all select 'California' state, 'Los Angeles' city, 'BB' categ, 2 nbr from dual
union all select 'California' state, 'Los Angeles' city, 'CC' categ, 3 nbr from dual
union all select 'California' state, 'San Diego' city, 'AA' categ, 4 nbr from dual
union all select 'California' state, 'San Diego' city, 'BB' categ, 5 nbr from dual
union all select 'California' state, 'San Diego' city, 'CC' categ, 6 nbr from dual
union all select 'California' state, 'San Francisco' city, 'AA' categ, 7 nbr from dual
union all select 'California' state, 'San Francisco' city, 'BB' categ, 8 nbr from dual
union all select 'California' state, 'San Francisco' city, 'CC' categ, 9 nbr from dual
union all select 'MP' state, 'Bhopal' city, 'AA' categ, 9 nbr from dual
union all select 'MP' state, 'Bhopal' city, 'BB' categ, 1 nbr from dual
union all select 'MP' state, 'Bhopal' city, 'CC' categ, 2 nbr from dual
union all select 'MP' state, 'Indore' city, 'AA' categ, 9 nbr from dual
union all select 'MP' state, 'Jabalpur' city, 'AA' categ, 9 nbr from dual)
select state,
       city,
       categ,
       sum(nbr) total
 from t
 group by state,rollup(city, categ)
-- group by cube (state,city, categ) having grouping(STATE) = 0
order by state, city, categ;

==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================