CASE WHEN with GROUP BY

Question

I have a table that looks like this (oversimplified case of the actual case to make my point)

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, currency

The data that should be produced by the query

shop_id     |   POUND   |   USDOLLAR    
-----------------------------------------
    1       |   400     |   300
-----------------------------------------
    2       |   250     |   100
-----------------------------------------
    3       |   400     |   100
-----------------------------------------

Answer 1

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_id

Answer 2

You're close but reversed SUM and CASE. Try:

SUM(CASE WHEN currency= "GBP" THEN price ELSE 0 END) AS POUND,