sql server programming: joining two tables -- sql server programming

Re: joining two tables

Arnie Rowland
6/22/2006 11:07:36 AM

sql server programming: How could we possible know?

You did not provide table DDL, and you did not provide data in the form of
INSERT statements. so the best you should expect is suggestions in the
'right' direction.

If you want better help, provide better information.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

[quoted text, click to view]

Re: joining two tables

Roy Harvey
6/22/2006 11:12:14 AM

SELECT Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
FROM (select * from tblTable1
UNION ALL
select * from tblTable2) as T

Roy Harvey
Beacon Falls, CT

[quoted text, click to view]

Re: joining two tables

Roy Harvey
6/22/2006 12:03:06 PM

Did you try it and get unexpected results? Perhaps I do not
understand your requirements. I did not build your tables and write
INSERTS for your test data to test it, but yes, I think it works.

It is certainly not the only way to write it. If both tables always
have exactly the same years, one alternative would be:

SELECT T1.Year,
T1.SerialA + T2.SerialA as SerialA,
T1.SerialB + T2.SerialB as SerialB
FROM (select Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
from tblTable1
group by Year) as T1
JOIN (select Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
from tblTable2
group by Year) as T2
ON T1.Year = T2.Year

Roy Harvey
Beacon Falls, CT

[quoted text, click to view]

joining two tables

magix
6/22/2006 11:01:10 PM

Hi

let say I have record:s in two tables, namely tblTable1 and tblTable2

tblTable1:
Year SerialA SerialB
1998 1 3
2000 3 2
1999 2 2
2001 5 3
1998 1 1
1999 2 1
2001 3 2

tblTable2:
Year SerialA SerialB
1998 2 0
1999 1 2
1999 0 2
2001 3 3
1998 2 2
1999 0 1
2001 4 2

I want to have it "Group by Year Order by Year" by joining these two tables
and sum each serial for each year.

The output will be
Year SerialA SerialB
1998 6 6
1999 5 8
2000 3 2
2001 15 10

What will be the SELECT statement to achieve the ouput as mentioned above ?

Thank you.

Regards.

Re: joining two tables

magix
6/22/2006 11:33:55 PM

are you sure this is working ?

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.