CODEWITHSUNDEEP
sql-server
user1955215
user1955215

Reputation: 743

Combining results in two tables

There are two tables (Table1 and Table2) with identical columns. 

Customerno, amount

There is a third table Customers with columns:

Customerno, customername

Currently, the following two queries are run and the combined result is derived using Excel (vlookup etc) 

Select a.customerno, b.customername, sum(a.amount) 
FROM Table1 a join customers b on a.Customerno = b.Customerno
group by a.customerno, b.customername


Select a.customerno, b.customername, sum(a.amount) 
FROM Table2 a join customers b on a.Customerno = b.Customerno
group by a.customerno, b.customername

Would would be the correct way of combining the two queries into one to get the desired result?

Upvotes: 1

Views: 64

Answers (2)

Nathan Dunleavy
Nathan Dunleavy

Reputation: 71

You need to use UNION statement for this. Here is the simplest way to union these two and you can work your way from here.

select *
from
(

Select a.customerno, b.customername, sum(a.amount) 
FROM Table1 a join customers b on a.Customerno = b.Customerno
group by a.customerno, b.customername

union -- or union all if you want to keep the duplicates

Select a.customerno, b.customername, sum(a.amount) 
FROM Table2 a join customers b on a.Customerno = b.Customerno
group by a.customerno, b.customername
) P

Upvotes: 0

Preet Sangha
Preet Sangha

Reputation: 65496

Select a.customerno, b.customername, sum(a.amount) 
FROM 
( 
   SELECT Customerno, amount FROM Table1
   UNION ALL -- use this keep duplicates ie amounts from both tables
   -- UNION -- will discard duplicates 
   SELECT Customerno, amount FROM Table2
) a 
join customers b on a.Customerno = b.Customerno
group by a.customerno, b.customername

Upvotes: 1

Related Questions