Grouping items for Count and SUM

1062283

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: 

CITY	AREA	PROD	REVENUE
NYC	N12	A	100
NYC	N12	A	100
NYC	N12	B	200
NYC	N11	A	50
NYC	N11	A	600
NYC	N11	B	300
AUS	A99	C	0
AUS	A88	B	150
AUS	A88	B	150
AUS	A88	A	300
AUS	A55	A	100
AUS	A55	A	200

Summary Table:

CITY_CODE	AREA_CODE	PRODUCT	PROD_COUNT	PROD_REV
NYC	N12	A	2	200
NYC	N12	B	1	200
NYC	N11	A	2	650
NYC	N11	B	1	300
AUS	A99	C	1	0
AUS	A88	A	1	300
AUS	A88	B	2	300
AUS	A55	A	2	300

Lothar Flatz

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.