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 |