CODEWITHSUNDEEP
sqldb2
Alda
Alda

Reputation: 27

How can I aggregate two different things on one table in DB2?

Let's say I have a table of agents, deals they make (buying or selling) and the amount of each deal:

+------+--------+---------+
| name |  deal  |  amount |
+------+--------+---------+
| alice| buy    |  10     |
| bob  | sell   |  5      |
| bob  | sell   |  8      |
| alice| sell   |  12     |
| alice| buy    |  9      |
| carol| buy    |  7      |
| bob  | buy    |  6      |
+------+--------+---------+

If I want to show for each how much they bought - easy:

SELECT name, SUM(amount) AS bought
FROM   table
WHERE  deal = 'buy'
GROUP BY name

The result would be: 

name  | bought
------+---
alice | 19 
bob   | 6
carol | 7

Same thing for selling. But what if I want to select BOTH as separate columns? I want this:


name  | bought | sold
------+--------+-----
alice | 19     | 12
bob   | 6      | 13
carol | 7      | 0

I'm completely stumped. Help please?

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use conditional aggregation:

SELECT name,
       SUM(CASE WHEN deal = 'buy' THEN amount ELSE 0 END) AS bought,
       SUM(CASE WHEN deal = 'sell' THEN amount ELSE 0 END) AS sold
FROM   table
GROUP BY name

Upvotes: 3

Related Questions