How to use left join in SQL to extract results?-CodePudding

The task is to generate a summary that consists of sums of the value of goods imported and exported by every country. Note that when a company buys some goods, it contributes to its country's total import, and when the company sells some goods, it contributes to its country's total export.

Write SQL query that return table consisting of three columns: country, export, import, which contain the sums of the values of the exported and imported goods for every country. Each country should appear in this table and result should be sorted increasingly by country.

Two tables are as follows:

The query should return:

Assume that:

There is no trade between companies within a single country.
Every company in table trades also appears in table companies.
Every company appears in table companies exactly once.

I have tried the following query but it isn't returning the correct result as shown above.

SELECT cd.country, SUM(exports.value) AS export, SUM(imports.value) AS import
  FROM companies cd 
  LEFT JOIN trades exports
    ON cd.country= exports.seller
  LEFT JOIN trades imports
    ON cd.country = imports.buyer
  GROUP BY cd.country
  ORDER BY cd.country

CodePudding user response：

A simple solution is calculate exports and imports separately and combine them:

select country, min(export) as export, min(import) as import
from (
    select companies.country, sum(value) as export, null as import
    from trades
    join companies on trades.seller = companies.name
    group by companies.country

    union all

    select companies.country, null, sum(value)
    from trades
    join companies on trades.buyer = companies.name
    group by companies.country
) as x
group by country

CodePudding user response：

You can LEFT JOIN twice your "companies" table with your "trades" table, then apply the aggregation once on your buyers and sellers values. The COALESCE function is needed to handle NULL values.

SELECT companies.country,
       COALESCE(SUM(buyers.value ), 0) AS `export`,
       COALESCE(SUM(sellers.value), 0) AS `import` 
FROM      companies 
LEFT JOIN trades buyers 
       ON buyers.buyer = companies.name
LEFT JOIN trades sellers
       ON sellers.seller = companies.name
GROUP BY companies.country
ORDER BY country

You can find a MySQL demo here, but really this code will run for all the most common DBMSs.

Note: This solution has been built according to your description rather than the expected output you shared.