Roy Harvey
3/22/2007 12:16:00 AM
I suspect you are trying to treat the two tables as one and get the
count on the combined set of rows. If so any of these (untested)
alternatives should work.
SELECT X.Count1 + Y.Count2 as "Channel Count"
FROM (SELECT count(*) as X
FROM operations01.dbo.Usr_organizations
WHERE statusid = 3
AND organizationname not like 'x_deleted%'
AND typeid = 2) as Count1,
(SELECT count(*) as Y
FROM operations02.dbo.Usr_organizations
WHERE statusid = 3
AND organizationname not like 'x_deleted%'
AND typeid = 2) as Count2
SELECT (SELECT count(*) as X
FROM operations01.dbo.Usr_organizations
WHERE statusid = 3
AND organizationname not like 'x_deleted%'
AND typeid = 2)
+
(SELECT count(*) as Y
FROM operations02.dbo.Usr_organizations
WHERE statusid = 3
AND organizationname not like 'x_deleted%'
AND typeid = 2)
AS "Channel Count"
SELECT COUNT(RowsFromBoth) as "Channel Count"
FROM (SELECT 1 as X
FROM operations01.dbo.Usr_organizations
WHERE statusid = 3
AND organizationname not like 'x_deleted%'
AND typeid = 2
UNION ALL
SELECT 1 as X
FROM operations02.dbo.Usr_organizations
WHERE statusid = 3
AND organizationname not like 'x_deleted%'
AND typeid = 2) as RowsFromBoth
SELECT COUNT(*) as "Channel Count"
FROM (SELECT *
FROM operations01.dbo.Usr_organizations
UNION ALL
SELECT *
FROM operations02.dbo.Usr_organizations) as Combined
WHERE statusid = 3
AND organizationname not like 'x_deleted%'
AND typeid = 2
Roy Harvey
Beacon Falls, CT
On 21 Mar 2007 15:58:09 -0700, "Some1" <some1youknow@gmail.com> wrote:
>Hello,
>
>I have two databases on one sql server. How can I run a single query
>on two databases? both databases have identical tables.
>
>servername: DB1
>database1: operations01
>database2: operations02
>table: Usr_organizations
>
>I've put together a sql script to count channels from both databases
>combined, but failed to excute
>
>
>select count (*) as "Channel Count"
>from operations01.dbo.Usr_organizations,
>operations02.dbo.Usr_organizations
>where statusid = 3
>and organizationname not like 'x_deleted%'
>and typeid = 2