By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE country (`name` TEXT, `country` TEXT);
✓
CREATE TABLE trades (`id` INTEGER, `seller` TEXT, `buyer` TEXT, `value` INTEGER);
✓
INSERT INTO country (`name`, `country`) VALUES
('Alice s.p.', 'Wonderland'),
('Y-zap', 'Wonderland'),
('Absolute', 'Mathlands'),
('Arcus t.g.', 'Mathlands'),
('Lil Mermaid', 'Underwater Kingdom'),
('None at all', 'Nothingland');
✓
INSERT INTO trades (`id`, `seller`, `buyer`, `value`) VALUES
('20121107', 'Lil Mermaid', 'Alice s.p.', '10'),
('20123112', 'Arcus t.g.', 'Y-zap', '30'),
('20120125', 'Alice s.p.', 'Arcus t.g.', '100'),
('20120216', 'Lil Mermaid', 'Absolute', '30'),
('20120217', 'Lil Mermaid', 'Absolute', '50');
✓
SELECT c.country,
SUM(CASE WHEN buyer IS NOT NULL THEN value ELSE 0 END) buyer,
SUM(CASE WHEN seller IS NOT NULL THEN value ELSE 0 END) seller
FROM country c
LEFT JOIN (
SELECT buyer, null seller, value FROM trades
UNION ALL
SELECT null, seller, value FROM trades
) t ON c.name IN (t.buyer, t.seller)
GROUP BY c.country
| country | buyer | seller |
|---|---|---|
| Mathlands | 180 | 30 |
| Nothingland | 0 | 0 |
| Underwater Kingdom | 0 | 90 |
| Wonderland | 40 | 100 |
SELECT DISTINCT c.country,
SUM(CASE WHEN c.name = t.buyer THEN value ELSE 0 END) OVER (PARTITION BY c.country) buyer,
SUM(CASE WHEN c.name = t.seller THEN value ELSE 0 END) OVER (PARTITION BY c.country) seller
FROM country c LEFT JOIN trades t
ON c.name IN (t.buyer, t.seller)
| country | buyer | seller |
|---|---|---|
| Mathlands | 180 | 30 |
| Nothingland | 0 | 0 |
| Underwater Kingdom | 0 | 90 |
| Wonderland | 40 | 100 |