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
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,820 views