How to add up all the Select results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to add up all the Select results

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2002-03-09 : 22:54:24

Hi, is it possible to perform this in 1 SQL statement :

A) Select ItemQTY, Date from Table_A where Item = XXX

Results:
1, 1/1/2002
2, 2/1/2002

B) Select ItemQTY, Date from Table_B where Item = XXX

Results:
3, 3/1/2002
4, 4/1/2002

C) Select ItemQTY, Date from Table_C where Item = XXX

Results:
5, 5/1/2002
6, 6/1/2002

Then add all the results and display them order by Date

Final Results:
1, 1/1/2002
2, 2/1/2002
3, 3/1/2002
4, 4/1/2002
5, 5/1/2002
6, 6/1/2002

Please help.

Thanks
Matthew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-09 : 22:59:12

You can use UNION or UNION ALL to combine each result together into one:

Select ItemQTY, Date from Table_A where Item = XXX
UNION ALL
Select ItemQTY, Date from Table_B where Item = XXX
UNION ALL
Select ItemQTY, Date from Table_C where Item = XXX

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-10 : 07:06:47

or
select ItemQTY, Date
from
(
select ItemQTY, Date from Table_A
union all
select ItemQTY, Date from Table_B
union all
select ItemQTY, Date from Table_C
) as a
where Item = XXX
order by Date

You could make the unions into a view - but should probably merge the tables into one.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources