Skip to Main Content

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.

Grouping items for Count and SUM

1062283May 18 2014 — edited May 19 2014

Hello,

I have a large table with thousands of records of data that shows revenues of products in many areas. I've simplified the table in "Detail Table".  My aim is to go through this detailed table and create another Summary table with the count of products and revenues of each product in each area.

So for each area in each city there will be one row PER product with one column for product count and one column for product revenue. I'm thinking of using SUM(CASE product WHEN 'A' THEN 1 ELSE 0 END) statement for measuring the count of products.

Any help on how to achieve this would be much appreciated.

Detail Table: 

CITYAREAPRODREVENUE
NYCN12A100
NYCN12A100
NYCN12B200
NYCN11A50
NYCN11A600
NYCN11B300
AUSA99C0
AUSA88B150
AUSA88B150
AUSA88A300
AUSA55A100
AUSA55A200

Summary Table:

CITY_CODEAREA_CODEPRODUCTPROD_COUNTPROD_REV
NYCN12A2200
NYCN12B1200
NYCN11A2650
NYCN11B1300
AUSA99C10
AUSA88A1300
AUSA88B2300
AUSA55A2300

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 15 2014
Added on May 18 2014
1 comment
973 views