Friday, October 4, 2019

CTE with full outer join example


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