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.

Expected resultset:

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?

Answers


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


Need Your Help

RSpec documentation…where is it hiding?

ruby-on-rails rspec rspec2

Having a terrible time finding RSpec-2 (and rspec-rails) documentation. Specifically I want to look up all available Matchers... but how?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.