With CountryCTE(CountryName,Export)
As (SELECT C.Country,ISNULL(sum(O.sumorders),0) as export
FROM [companies] C
full outer JOIN
(
SELECT seller,sum(value) AS sumorders
FROM [dbo].[trades]
GROUP BY seller
)O ON C.[name] = O.[seller]
GROUP BY C.Country),
MyCountryCTE(CountryName,Import)
As
(SELECT C.Country,ISNULL(sum(O.sumorders),0) as import
FROM [companies] C
full outer JOIN
(
SELECT buyer,sum(value) AS sumorders
FROM [dbo].[trades]
GROUP BY buyer
)O ON C.[name] = O.[buyer]
GROUP BY C.Country)
select CountryCTE.CountryName,Export,Import from CountryCTE full outer join MyCountryCTE on CountryCTE.CountryName=MyCountryCTE.CountryName
order by CountryCTE.CountryName
No comments:
Post a Comment