CODEWITHSUNDEEP
mysql
tomDev
tomDev

Reputation: 5760

MYSQL: Group By and SUM different columns with different criteria

I'm trying to query a MySQL table with GROUP BY, and then SUM columns based on different criteria (type).

Here is a table:

NAME       TYPE      AMOUNT

Walter     BUY       2
Walter     BUY       5
Walter     SELL      3
Walter     SELL      1
Pollo      BUY       8 
Pollo      BUY       2 
Jessie     BUY       1
Jessie     BUY       3
Jessie     SELL      2
Jessie     SELL      1

I'm want to group by name and SUM the BUY and SELL for each name. The result I'm looking for:

Name       SUM(BUY)    SUM(SELL)

Jessie     4           3
Pollo      10          0
Walter     7           4

I'm able to achieve the SUM but only on two different queries:

 SELECT name, SUM(BUY) FROM tb WHERE TYPE = "BUY" GROUP BY NAME;

 SELECT name, SUM(SELL) FROM tb WHERE TYPE = "SELL" GROUP BY NAME;

I'm trying to UNION and UNION ALL these queries but it's not working:

 (SELECT name, SUM(BUY) FROM tb WHERE TYPE = "BUY" GROUP BY NAME)
 UNION
 (SELECT name, SUM(SELL) FROM tb WHERE TYPE = "SELL" GROUP BY NAME);

Is it possible to GROUP BY and then SUM different columns with different WHERE condition?

Upvotes: 1

Views: 37

Answers (1)

D-Shih
D-Shih

Reputation: 46219

We can try to use the condition aggregate function

use SUM and CASE WHEN expressions with your logic.

SELECT name, 
     SUM(CASE WHEN TYPE = 'buy' THEN AMOUNT ELSE 0 END) 'SUM(BUY)',
     SUM(CASE WHEN TYPE = 'Sell' THEN AMOUNT ELSE 0 END) 'SUM(Sell)'
FROM tb  
WHERE TYPE IN ('buy','Sell')
GROUP BY NAME;

sqfliddle

Upvotes: 2

Related Questions