Internal join and group

Question

I have two tables with the same definition.

T1: Name VARCHAR(50) Qty INT T2: Name VARCHAR(50) Qty INT 

This is the data that each table has:

 T1: Name Qty a 1 b 2 c 3 d 4 T2: Name Qty a 1 b 3 e 5 f 10 

I want to get a result that can sum Qty from both tables based on Name.

Expected results:

 Name TotalQty a 2 b 5 c 3 d 4 e 5 f 10 

If I do Left Join or Right Join, it does not return me the name from any of the tables.

What I think is to create a temporary table and add these records and just populate the SUM collection in the Qty column, but I think there should be a better way to do this.

This is what my query looks like, which does not return the expected result set:

 SELECT t1.Name, ISNULL(SUM(t1.Qty + t2.Qty),0) TotalQty FROM t1 LEFT JOIN t2 ON t1.Name = T2.Name GROUP BY t1.Name 

Can someone please tell me if the temporary table is being created correctly here, or is there a better way to do this?

Answer 1

You can use a full outer join:

 SELECT ISNULL(t1.Name, t2.Name) AS Name, ISNULL(t1.Qty, 0) + ISNULL(t2.Qty, 0) AS TotalQty FROM t1 FULL JOIN t2 ON t1.Name = T2.Name 

See how it works on the web: sqlfiddle

Answer 2

You can use UNION ALL to select both tables as one, since they have the same definition. From there, you can nest them in a view, and then SUM :

 SELECT [Name], SUM(Qty) AS TotalQty FROM ( SELECT [Name], Qty FROM t1 UNION ALL SELECT [Name], Qty FROM t2 ) YourDerivedTable GROUP BY [Name]