-1

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:

enter image description here

The query should return:

enter image description here

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
Gooner Coder
  • 25
  • 5
  • 1
    What's the database? – The Impaler Jul 05 '22 at 13:06
  • 2
    it looks like, according to the data you posted, there are discrepancies both source and expected result. What I mean: **'Wonderland'** SUM(Export data) in source = 130, but you mentioned in **'Wonderland'** = 100. Is possible to misinformation occurred. And tag DBMS. – HRK Jul 05 '22 at 13:29
  • Hi @GoonerCoder you are joining tables like this: cd.country= exports.seller and because of that your query is not returning nothing for columns export and import. There is no value in column country in table companies that matches values in columns seller or buyer in table trades... – VBoka Jul 05 '22 at 13:33
  • Also, please explain your data in table "The query should return:" because I do not understand how the data above can return this data ? – VBoka Jul 05 '22 at 13:35

1 Answers1

3

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
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Adjustments on my solution lead to your solution. This is the correct answer for the OP's problem. – lemon Jul 07 '22 at 09:36