|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
joining two tableslet 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 Show quote On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <ma***@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 ?
Show quote "Roy Harvey" <roy_har***@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" <ma***@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. >> >> 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 Show quote On Thu, 22 Jun 2006 23:33:55 +0800, "magix" <ma***@asia.com> wrote: >are you sure this is working ? > >"Roy Harvey" <roy_har***@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" <ma***@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. >>> >>> > 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. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "magix" <ma***@asia.com> wrote in message news:449ab86a_1@news.tm.net.my... > are you sure this is working ? > > "Roy Harvey" <roy_har***@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" <ma***@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. >>> >>> > >  |
|||||||||||||||||||||||
Other interesting topics