How to write SQL sub-query in SQL?

Question

Here is sample data I am looking for total buying trade value and total selling trades value based on country.

Here are two tables, country, and trades Table [companies]:

  +-------------+--------------------+
  |         name|            country |
  +-------------+--------------------+
  |  Alice s.p. |         Wonderland |
  |      Y-zap  |         Wonderland |
  |    Absolute |          Mathlands |
  |  Arcus t.g. |          Mathlands |
  | Lil Mermaid | Underwater Kingdom |
  | None at all |        Nothingland |
  +-------------+--------------------+

Table [trades]:

trades:
  +----------+-------------+------------+-------+
  |       id |      seller |      buyer | value |
  +----------+-------------+------------+-------+
  | 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 |
  +----------+-------------+------------+-------+

Expected Output:

  +--------------------+--------+--------+
  |             country|  buyer |  seller|
  +--------------------+--------+--------+
  |          Mathlands |    180 |     30 |
  |        Nothingland |      0 |      0 |
  | Underwater Kingdom |      0 |     90 |
  |         Wonderland |     40 |    100 |
  +--------------------+--------+--------+

I am trying this: It gives only one value column and it doesn't show the 0 trade country that I want to show also.

select country, sum(value), sum(value) 
from
(select a.buyer as export, a.seller as import, value, b.country as country
from trades as a
join companies as b 
on a.seller=b.name)

group by country 
order by country

Answer 1

Join country to distinct rows of trades which contain only buyer or seller and aggregate conditionally:

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

Or, with SUM() window function:

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)

See the demo.