Forum Stats

  • 3,784,122 Users
  • 2,254,894 Discussions
  • 7,880,697 Comments

Discussions

Grouping items for Count and SUM

1062283
1062283 Member Posts: 5
edited May 19, 2014 3:47AM in SQL & PL/SQL

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

Answers

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    HI,

    Sorry, thousends of rows is not big. Not even 2 digit million numbers would be by my standards.

    Do answer your question:

    select CITY,    AREA,    PROD,    count(*) PROD_COUNT, sum( REVENUE) PROD_REV

      from  detail_table

    group by  CITY,    AREA,    PROD;

    should be the answer if I got you right. That is fairly standard sql.

    Lothar Flatz
This discussion has been closed.