Trading balance between countries - Northwind database

Question

I have quite a task to do, using old Northwind database, I need to create a trading balance for each country. Which means, I need to count, how much each country sold to each country, how much they've bought and what's the balance, on plus or minus. I have a table Countries with columns 'CountryID, CountryName, ContinentID'. I'd prefer if countries would be named by their original name, not CountryID.

I have table Suppliers with columns like SupplierID with matching CountryId. The same thing goes for Customers table, CustomerID and CountryId.

So far I have this:

SELECT c.CountryID, SUM(CONVERT(money, (od.UnitPrice * od.Quantity) * (1 - od.Discount) / 100) * 100) AS ProductSales
From Countries c
LEFT OUTER JOIN Suppliers s ON c.CountryID = s.CountryID
JOIN Customers cu ON c.CountryID = cu.CountryID
JOIN Products p ON s.SupplierID = p.SupplierID
JOIN Orders o ON cu.CustomerID = o.CustomerID
JOIN [Order Details] od ON o.OrderID = od.OrderID
GROUP BY c.CountryID

RESULTS:

9   2072561.58
6   65322.04
7   56430.15
4   106925.77
19  412799.24
13  17205.45
5   200785.20
16  35966.40
17  272475.70
11  78850.80
20  2947015.56
8   406791.55

I don't think it gives me anything close to what I need, probably it's only the value of all sold products. I am stuck with this task, I'l provide any informations you need.

Answer 1

It will look like as shown below (Sorry, I don't have this DB installed on my local box). May be it will help-

select
    fc.Countryname as FromCountryName,
    tc.Countryname as ToCountryName,
    SUM(CONVERT(money, (od.UnitPrice * od.Quantity) * (1 - od.Discount) / 100) * 100)
from Suppliers as s
inner join Countries as fc on fc.id = s.countryid
inner join Products as p on p.supplierid = s.id
inner join [Order Details] as od on od.productid = p.id
inner join Orders as o on o.id = od.OrderId
inner join Customers as cu on cu.id = o.customerId
inner join Countries as tc on tc.id = cu.countryid
group by fc.Countryname, tc.Countryname

We have Group By the data for FromCountry and ToCountry.

For second requirement as per below comments (Maybe below query not efficient in terms of performance) -

select
    max(fc.name) as CountryA,
    min(tc.name) as CountryB,
    BalanceAB = sum(case when fc.name > tc.name then (CONVERT(money, (od.UnitPrice * od.Quantity) * (1 - od.Discount) / 100) * 100) end),
    BalanceBA = sum(case when fc.name < tc.name then (CONVERT(money, (od.UnitPrice * od.Quantity) * (1 - od.Discount) / 100) * 100) end),
    SUM((case when fc.name < tc.name then -1 else 1 end) * CONVERT(money, (od.UnitPrice * od.Quantity) * (1 - od.Discount) / 100) * 100) as [Total(A - B)]
from @Supplier as s
inner join @Country as fc on fc.id = s.countryid
inner join @Product as p on p.supplierid = s.id
inner join @OrderDetail as od on od.productid = p.id
inner join @Order as o on o.id = od.OrderId
inner join @Customer as cu on cu.id = o.customerId
inner join @Country as tc on tc.id = cu.countryid
group by (case when fc.name < tc.name then fc.name+' to '+tc.name else tc.name+' to '+fc.name end)

Output -

CountryA    CountryB    BalanceAB   BalanceBA   Total(A - B)
US          UK          20.00       200.00      -180.00