How to join two SQL queries in SQL Server?

Question

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?

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (almost 30 years ago) and its use is discouraged — marc_s
– marc_s, Commented Dec 30, 2021 at 6:23
JOIN? Show us some sample result from each query, and also the desired combined result. — jarlh
– jarlh, Commented Dec 30, 2021 at 9:24
Why do you have different income tables for each year? With one, common income table you wouldn't have this problem. — jarlh
– jarlh, Commented Dec 30, 2021 at 9:28

Mehman967 · Accepted Answer · 2021-12-30 05:43:13Z

2

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

'''

edited Dec 30, 2021 at 5:43

answered Dec 30, 2021 at 5:37

Mehman967

815 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jarlh Over a year ago

Tip of today: COALESCE() - the ANSI/ISO way do to NVL, ISNULL etc.

Sathiya · Accepted Answer · 2021-12-30 05:32:07Z

0

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

answered Dec 30, 2021 at 5:32

Sathiya

2392 silver badges2 bronze badges

Collectives™ on Stack Overflow

How to join two SQL queries in SQL Server?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related