1

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
-----------------------------------------
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
1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.