combining results from 2 databases into one statement

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)

combining results from 2 databases into one statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-23 : 08:50:05

Phil writes "How would I combine this statement into 1 count?
select count(*)
from logsfall2002..da_meal_transactions_log
union all
select count(*)
from da_meal_transactions_log

Notice I'm selecting from 2 different databases. Also, there will be more difficulty in this statement I'm going to have group by's and order by's. I really want to have this query added between 2 databases:

select stationHall,mealfunctionName,'MealCount' = count(*)
from da_meal_transactions_log
where returnstatus in (1,4)
group by stationHall,mealfunctionName
order by stationhall,mealfunctionName

I want to add the 'MealCount' from the 2 databases and have them be only 1 count using a union all. Is this possible?

Thanks,
Phil"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-01-23 : 09:00:31

something along the lines of

select sum(ab) from (
select count(*) as ab from db1.table1
union
select count(*) as ab from db2.table2) a

should work....there are other examples floating around here....searching for "linked servers" and "union" may be useful....

BOL should also have some info....

tacket
Starting Member

47 Posts

Posted - 2003-01-23 : 12:02:29

THANKS A BUNCH! That totally worked. Here was the final product in case other people are curious.

select stationHall, mealfunctionName, 'Mealcount' = sum(mealcount)
from
(
select stationHall, mealfunctionName, count(*) as mealcount
from da_meal_transactions_log
where returnstatus in (1,4)
group by stationHall,mealfunctionName
union
select stationHall, mealfunctionName, count(*) as mealcount
from logsFALL2002..da_meal_transactions_log
where returnstatus in (1,4)
group by stationHall,mealfunctionName
) a
group by stationHall,mealfunctionName
order by stationhall,mealfunctionName

Thanks,
Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources