Need to subtract two queries using SQL-CodePudding

I have two select statements which are very similar. One produces a table with about 30 different years and calculates the total number of exports in that year using group_by. The second query produces a similar table but with total imports for each year. I would like to use these two queries to produce a third query that would produce a 3rd table with the same years listed only the next column is the subtraction of exports-imports for each year. How would I do this?

Code fort table of exports:

SELECT year,
       Sum(amount) AS TE
FROM   tradeinfo
WHERE  tradetype LIKE 'Export'
GROUP  BY year
ORDER  BY year

Code for table of imports:

SELECT year,
       Sum (amount) AS TI
FROM   tradeinfo
WHERE  tradetype LIKE 'Import'
GROUP  BY year
ORDER  BY year

CodePudding user response：

you can use those two queries as a common table expression or subquery and join them by year

with 
  exports(year, te) as (
    select year, sum(amount) 
    from TradeInfo 
    where TradeType like 'Export' 
    group by year),
  imports(year, ti) as (
    select year, sum(amount) 
    from TradeInfo 
    where TradeType like 'Import' 
    group by year)
select e.year, te-ti
  from exports e inner join imports i on e.year = i.year
  order by e.year

CodePudding user response：

you should use Subqueries to join queries in order to subtract Export and Import.

SELECT Export.year,
       te - ti
FROM   (SELECT year,
               Sum(amount) AS TE
        FROM   tradeinfo
        WHERE  tradetype LIKE 'Export'
        GROUP  BY year) Export
       JOIN (SELECT year,
                    Sum (amount) AS TI
             FROM   tradeinfo
             WHERE  tradetype LIKE 'Import'
             GROUP  BY year) Import
         ON Export.year = Import.year

The other way is using consecutive CTEs

;WITH export
     AS (SELECT year,
                Sum(amount) AS TE
         FROM   tradeinfo
         WHERE  tradetype LIKE 'Export'
         GROUP  BY year),
     import
     AS (SELECT year,
                Sum (amount) AS TI
         FROM   tradeinfo
         WHERE  tradetype LIKE 'Import'
         GROUP  BY year)
SELECT export.year,
       te - ti
FROM   export
       JOIN import
         ON export.year = import.year

CodePudding user response：

You can get the sum by using a conditional case expression.

Without knowing your data I wouldn't suggest an inner join of each result set as that will eliminate any group where there are no import or export rows, a full join would be preferable there, although compared to a single pass with case it would be poor-performing. You may need to use isnull to handle null values.

select year, 
        Sum(case when tradetype='Import' then amount end) 
          - Sum(case when tradetype='Export' then amount end) [Import - Export]
from   tradeinfo
where  tradetype in ('Import','Export') /* Remove this if tradetype has no other values */
group  by year
order  by year