Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

GROUP BY ROLLUP with Grouped Category Subtotals

682388Mar 24 2009 — edited Aug 6 2009
Hi,

I need the categories to have group totals like the example below. Notice that California has it's own category subtotals then a grand total.

I could do this with unions, a row type column, and sorting, but that would mean reading the entire table for each total snd subtotal.

Any suggestions?

Thank You in Advance for Your Help,

Lou


My Data

STATE CITY CATEGORY NBR
California Los Angeles AA 1
California Los Angeles BB 2
California Los Angeles CC 3
California San Diego AA 4
California San Diego BB 5
California San Diego CC 6
California San Francisco AA 7
California San Francisco BB 8
California San Francisco CC 9


Desired Result

STATE CITY CATEGORY NBR
California Los Angeles AA 1
California Los Angeles BB 2
California Los Angeles CC 3
California Los Angeles 6
California San Diego AA 4
California San Diego BB 5
California San Diego CC 6
California San Diego 16
California San Francisco AA 7
California San Francisco BB 8
California San Francisco CC 9
California San Francisco 24
California AA 12
California BB 15
California CC 18
Grand Total 45
This post has been answered by Frank Kulash on Mar 24 2009
Jump to Answer

Comments

fsitja
Probably this: 
SQL> with t as (select 'California' state, 'Los Angeles' city, 'AA' categ, 1 nbr from dual
  2  union all select 'California' state, 'Los Angeles' city, 'BB' categ, 2 nbr from dual
  3  union all select 'California' state, 'Los Angeles' city, 'CC' categ, 3 nbr from dual
  4  union all select 'California' state, 'San Diego' city, 'AA' categ, 4 nbr from dual
  5  union all select 'California' state, 'San Diego' city, 'BB' categ, 5 nbr from dual
  6  union all select 'California' state, 'San Diego' city, 'CC' categ, 6 nbr from dual
  7  union all select 'California' state, 'San Francisco' city, 'AA' categ, 7 nbr from dual
  8  union all select 'California' state, 'San Francisco' city, 'BB' categ, 8 nbr from dual
  9  union all select 'California' state, 'San Francisco' city, 'CC' categ, 9 nbr from dual)
 10  select state, 
 11         city,
 12         categ,
 13         sum(nbr) total
 14   from t
 15   group by rollup (state, city, categ)
 16  order by state, city, categ
 17  /

STATE      CITY          CA      TOTAL
---------- ------------- -- ----------
California Los Angeles   AA          1
California Los Angeles   BB          2
California Los Angeles   CC          3
California Los Angeles               6
California San Diego     AA          4
California San Diego     BB          5
California San Diego     CC          6
California San Diego                15
California San Francisco AA          7
California San Francisco BB          8
California San Francisco CC          9

STATE      CITY          CA      TOTAL
---------- ------------- -- ----------
California San Francisco            24
California                          45
                                    45

14 linhas selecionadas.

SQL>
682388
Hi fsitja,

Thank you for your prompt reply but that's not providing the category totals for all of California.

Lou
fsitja
My bad, that would be a cube then 
SQL> with t as (select 'California' state, 'Los Angeles' city, 'AA' categ, 1 nbr from dual
  2  union all select 'California' state, 'Los Angeles' city, 'BB' categ, 2 nbr from dual
  3  union all select 'California' state, 'Los Angeles' city, 'CC' categ, 3 nbr from dual
  4  union all select 'California' state, 'San Diego' city, 'AA' categ, 4 nbr from dual
  5  union all select 'California' state, 'San Diego' city, 'BB' categ, 5 nbr from dual
  6  union all select 'California' state, 'San Diego' city, 'CC' categ, 6 nbr from dual
  7  union all select 'California' state, 'San Francisco' city, 'AA' categ, 7 nbr from dual
  8  union all select 'California' state, 'San Francisco' city, 'BB' categ, 8 nbr from dual
  9  union all select 'California' state, 'San Francisco' city, 'CC' categ, 9 nbr from dual)
 10  select state, 
 11         city,
 12         categ,
 13         sum(nbr) total
 14   from t
 15   group by cube(state, city, categ)
 16  having (grouping(city) = 1 and grouping(state) = 0)
 17  or (grouping(categ) = 0 and grouping(city) = 0 and grouping(state) = 0)
 18  order by state, city, categ
 19  /

STATE      CITY          CA      TOTAL
---------- ------------- -- ----------
California Los Angeles   AA          1
California Los Angeles   BB          2
California Los Angeles   CC          3
California San Diego     AA          4
California San Diego     BB          5
California San Diego     CC          6
California San Francisco AA          7
California San Francisco BB          8
California San Francisco CC          9
California               AA         12
California               BB         15

STATE      CITY          CA      TOTAL
---------- ------------- -- ----------
California               CC         18
California                          45

13 linhas selecionadas.

SQL>
You can play around with the having clause and groupings and select the rows as needed.

Regards,
Francisco.

Edited by: fsitja on Mar 24, 2009 6:11 PM

Edited by: fsitja on Mar 24, 2009 6:14 PM
Frank Kulash
Answer
Hi,
SELECT	  state, city, category
,	  SUM (nbr)			AS nbr
FROM	  table_x
GROUP BY  CUBE (state, city, category)
HAVING	  GROUPING (state)   = 0
ORDER BY  state, city, category;
Does basically what you want.

The numbers aren't quite what you posted.
The "Grand Total" row says "Califoirnia" instead. You can fix that with CASE and GROUPING.
Marked as Answer by 682388 · Sep 27 2020
682388
That's it Frank, thank you!

I can take care of the verbiage with CASE statements.
Aketi Jyuuzou
Frank Kulash wrote
GROUP BY  CUBE (state, city, category)
HAVING    GROUPING (state)   = 0
There is another one ;-)
select state, city,categ,
       sum(nbr) total
 from t
 group by state,cube(city,categ)
order by state, city, categ;
1 person found this helpful
682388
That's even better Aketi!

Now I don't have to filter out totals I don't need.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 24 2009
Added on Mar 24 2009
7 comments
2,834 views