I have 2 tables Comapnies and Trades: Comapnies:
| Country | center |
|---|---|
| Absolute | Mathlands |
| Alice s.p. | Wonderland |
| Arcus t.g. | Mathlands |
| Lil Mermaid | Underwater Kingdom |
Trades:
| id | seller | buyer | value |
|---|---|---|---|
| 20120125 | Alice s.p. | Arcus t.g. | 100 |
| 20120216 | Lil Mermaid | Absolute | 30 |
| 20120217 | Lil Mermaid | Absolute | 50 |
| 20121107 | Lil Mermaid | Alice s.p. | 10 |
Query I used:
select country
,(select sum(value) from trades t1 where c.name = t1.buyer) as imports
,(select sum(value) from trades t2 where c.name = t2.seller) as exports
from companies c
group by country
order by country;
I am not getting the output as required.
required output:
| country | Export | Import |
|---|---|---|
| Mathlands | 30 | 180 |
| Nothingland | 0 | 0 |
| Underwater Kingdom | 90 | 0 |
| Wonderland | 100 | 40 |
What I am getting:
| country | Imports | Exports |
|---|---|---|
| Mathlands | 80 | NULL |
| Nothingland | NULL | NULL |
| Underwater Kingdom | NULL | 90 |
| Wonderland | 10 | 100 |
CodePudding user response：
You can use a conditional aggregation
select c.center
, sum(case c.Country when t.seller then value end) export
, sum(case c.Country when t.buyer then value end) import
from Comapnies c
left join Trades t on c.Country in (t.seller, t.buyer)
group by c.center
order by c.center;
I'm using col names from your sample data.