how to sum two columns in postgresql?

Question

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?

krokodilko · Accepted Answer · 2019-04-21 05:42:15Z

3

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

answered Apr 21, 2019 at 5:42

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

krokodilko Over a year ago

c.company IN (t.seller, t.buyer) is a short for c.company = t.seller OR c.company = t.buyer.

Gordon Linoff · Accepted Answer · 2019-04-21 11:40:25Z

1

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;

answered Apr 21, 2019 at 11:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

how to sum two columns in postgresql?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related