How to join two SQL queries in SQL Server?-CodePudding

I have two SQL queries, this is the first one:

SELECT Activity, SUM(Amount) AS "Total Amount 2009"
FROM Activities, Incomes
WHERE Activities.UnitName = ? 
  AND Incomes.ActivityId = Activities.ActivityID
GROUP BY Activity
ORDER BY Activity;

Second query:

SELECT Activity, SUM(Amount) AS "Total Amount 2008"
FROM Activities, Incomes2008
WHERE Activities.UnitName = ? 
  AND Incomes2008.ActivityId = Activities.ActivityID
GROUP BY Activity
ORDER BY Activity;

How to join these two SQL queries?

CodePudding user response：

I think it will help you. If your RDBMS Oracle DB use NVL instead of ISNULL. Use FULL JOIN Because if you have Activity in one Query and this Activity not exist in other you can lost this Activity. '''SQL

SELECT 
 ISNULL(T1.Activity,T2.Activity) AS Activity
,ISNULL(TotalAmount2009,0) AS TotalAmount2009
,ISNULL(TotalAmount2008,0) AS TotalAmount2008
FROM
(select Activity, SUM(Amount) as "TotalAmount2009"
from Activities, Incomes
where Activities.UnitName = ? AND
  Incomes.ActivityId = Activities.ActivityID
GROUP BY Activity) T1
FULL JOIN 
(select Activity, SUM(Amount) as "TotalAmount2008"
from Activities, Incomes2008
where Activities.UnitName = ? AND
  Incomes2008.ActivityId = Activities.ActivityID
GROUP BY Activity) T2 
ON T1.Activity=T2.Activity

'''

CodePudding user response：

use the below query to join two queries.

SELECT t1.Activity, t1."Total Amount 2009", t2."Total Amount 2008" FROM(query1) as t1, (query2) as t2 WHERE t1.Activity = t2.Activity