CODEWITHSUNDEEP
sqlpostgresql
Luis
Luis

Reputation: 1475

how to sum two columns in postgresql?

I have the following tables:

COMPANIES:

company country
A        us
B        UK
C        france

Trades:

seller   buyer   amount
 A         B       10
 B         C       12

I want to get the summary of buying and selling per country. You can assume no transaction within the same country

country  sum_buyer   sum_seller
us                    10
UK         10         12
france     12

How can I do that?

Upvotes: 1

Views: 1366

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

For efficiency, it might be better to pivot the data rather than using in or or in a join. So, I would recommend:

select c.country,
       sum(amount_buyer) as sum_buyer,
       sum(amount_seller) as sum_seller
from trades t cross join lateral
     (values (t.seller, t.amount, 0),
             (t.buyer, 0, t.amount)
     ) v(company, amount_seller, amount_buyer) join
     companies c
     on c.company = v.company
group by c.country;

Upvotes: 1

krokodilko
krokodilko

Reputation: 36127

A kind of pivot query should do this task:

SELECT c.country,
       sum(case when c.company = t.buyer then amount end) as sum_buyer,
       sum(case when c.company = t.seller then amount end) as sum_seller
FROM trades t
JOIN companies c ON c.company IN (t.seller, t.buyer)
GROUP BY c.country
ORDER BY 1 DESC

Demo: http://www.sqlfiddle.com/#!15/7458d2/15

| country | sum_buyer | sum_seller |
|---------|-----------|------------|
|      us |    (null) |         10 |
|      UK |        10 |         12 |
|  france |        12 |     (null) |

Upvotes: 3

Related Questions