inner join and group by
I have two tables with identical definition.
T1: Name VARCHAR(50) Qty INT T2: Name VARCHAR(50) Qty INT
This is the data 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 have result which can sum the Qty from both the tables based on Name.
Name TotalQty a 2 b 5 c 3 d 4 e 5 f 10
If am do Left Join or Right Join, it is not going to return me the Name from either of the tables.
What i am thinking is to create a temp table and add these records and just do a SUM aggregate on Qty column but i think there should be a better way to do this.
This is how my query looks like which does not return the expected resultset:
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 creating a temp table is OK here or there is a better way to do this?
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 it working online: sqlfiddle