I'm using Oracle SQL-developer and I got the following output-table, which shows the monthly sales value of our customers. The customers have several locations.
| month | year | customer_name | sales_volume |
|---|---|---|---|
| 01 | 2022 | Farming company Berlin | 150 |
| 01 | 2022 | Farming company London | 200 |
| 01 | 2022 | Farming company Amsterdam | 350 |
| 01 | 2022 | XY Company Berlin | 200 |
| 01 | 2022 | customer 5 | 7 |
| 01 | 2022 | customer 7 | 7 |
| 01 | 2022 | X_Person | 2 |
| 02 | 2022 | XY Company London | 100 |
| 02 | 2022 | Hello Company Berlin | 150 |
| 02 | 2022 | Hello Company Amsterdam | 150 |
| 02 | 2022 | customer 1 | 20 |
| 02 | 2022 | customer 2 | 10 |
| 02 | 2022 | customer 3 | 5 |
| 02 | 2022 | Y-Person | 1 |
Now I'd like to get the sales_volume per customer_name for month/year. I want to add the sales_volume per month/year for all the different locations of the Farming company, the XY Company and the Hello Company. The rest (customer 1-7, X-Person, Y-Person) should be summed up in an own row named "Other"
The new output table would be the following:
| month | year | customer_name | sum_Sales_volume |
|---|---|---|---|
| 01 | 2022 | Farming Company | 700 |
| 01 | 2022 | XY Company | 300 |
| 01 | 2022 | Other | 16 |
| 02 | 2022 | XY Company | 100 |
| 02 | 2022 | Hello Company | 300 |
| 02 | 2022 | Other | 36 |
So far I tried to sum the customer_name with LIKE function but I don't understand how the "when then" works in this case.
My code:
Select
month,
year,
sum(sales_volume)
CASE
WHEN customer_name LIKE '