CODEWITHSUNDEEP
sql-servergroupingcalculation
user11852059
user11852059

Reputation:

How to group the results of a calculation

I have two tables: dbo.company_data which contains different companies and their country of origin, and the second table dbo.sellers_data which has the units sold to each other by these companies.

first table:

second table:

What I need to obtain is: a table consisting of three columns - country, export, import, which contain the sums of the values of the exported (sold to other countries) and imported (purchased from other countries) goods for every country. Each country should appear in this table.

Upvotes: 0

Views: 3413

Answers (2)

rboling
rboling

Reputation: 727

Try something like this

  
  SELECT cd.Country, SUM(exports.units) AS export, SUM(imports.units) AS import
  FROM dbo.company_data cd
  LEFT JOIN dbo.sellers_data exports
    ON cd.Country = exports.Seller
  LEFT JOIN dbo.sellers_data imports
    ON cd.Country = imports.buyer
  GROUP BY cd.Country
  ORDER BY cd.Country

Upvotes: 0

zealous
zealous

Reputation: 7503

You need to aggregate import and export units using two joins and aggregated function sum.

select
  country,
  coalesce(sum(se.units), 0) as export_qty,
  coalesce(sum(si.units), 0) as import_qty
from company_data c

left join seller_data se
on c.company_name = se.seller

left join seller_data si
on c.company_name = si.buyer

group by
  country

Upvotes: 2

Related Questions