joining two tables

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.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
On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <magix@.asia.com> wrote:

>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.
>|||are you sure this is working ?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
> 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
> On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <magix@.asia.com> wrote:
>|||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
On Thu, 22 Jun 2006 23:33:55 +0800, "magix" <magix@.asia.com> wrote:

>are you sure this is working ?
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
>|||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
"magix" <magix@.asia.com> wrote in message news:449ab86a_1@.news.tm.net.my...
> are you sure this is working ?
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
>sql