patternsqlMinor
CASE WHEN with GROUP BY
Viewed 0 times
casewithgroupwhen
Problem
I have a table that looks like this (oversimplified case of the actual case to make my point)
Now I need to make a query that will give me something like this table, grouping by shops yet giving sum of sales per currency
My problem is when I make currency column non aggregate it fails because my query becomes non standard. When I add currency to group by then each currency creates new row, something I don't want.
I have run out of option and would appreciate any help or pointer to the right direction.
I use MySQL but would appreciate if I would get the solution that works with more databases (PostGreSQL being at the top of the list after MySQL)
Query that works with undesired results
The data that should be produced by the query
CREATE TABLE sales (
id int,
currency VARCHAR(5),
price int
shop_id int
);Now I need to make a query that will give me something like this table, grouping by shops yet giving sum of sales per currency
My problem is when I make currency column non aggregate it fails because my query becomes non standard. When I add currency to group by then each currency creates new row, something I don't want.
I have run out of option and would appreciate any help or pointer to the right direction.
I use MySQL but would appreciate if I would get the solution that works with more databases (PostGreSQL being at the top of the list after MySQL)
Query that works with undesired results
SELECT shop_id,
(CASE WHEN currency= "GBP" THEN SUM(price) ELSE 0 END) AS POUND,
(CASE WHEN currency= "USD" THEN SUM(price) ELSE 0 END) AS USDOLLAR
GROUP BY shop_id, currencyThe data that should be produced by the query
shop_id | POUND | USDOLLAR
-----------------------------------------
1 | 400 | 300
-----------------------------------------
2 | 250 | 100
-----------------------------------------
3 | 400 | 100
-----------------------------------------Solution
You almost get it, try by putting SUM() at the beginning of the CASE statement.
SELECT shop_id,
SUM(CASE WHEN currency= "GBP" THEN price ELSE 0 END) AS POUND,
SUM(CASE WHEN currency= "USD" THEN price ELSE 0 END) AS USDOLLAR
GROUP BY shop_idCode Snippets
SELECT shop_id,
SUM(CASE WHEN currency= "GBP" THEN price ELSE 0 END) AS POUND,
SUM(CASE WHEN currency= "USD" THEN price ELSE 0 END) AS USDOLLAR
GROUP BY shop_idContext
StackExchange Database Administrators Q#168540, answer score: 5
Revisions (0)
No revisions yet.