I've checked several references and can't find any syntax that works. Doing a UNION of two SELECT queries pulling the same field from two diferent tables:

SELECT fld1 FROM tbl1 WHERE [expression] UNION SELECT fld1 FROM tbl2 WHERE [expression]

I just want to count the number of records in the result set, using SELECT COUNT(*). Not sure what the right syntax is, but have a feeling there's something easy that I'm missing, maybe another way without using UNION. Any help much appreciated.

select count(*) from
(SELECT fld1 FROM tbl1 WHERE [expression] UNION SELECT fld1 FROM tbl2 WHERE [expression])

Yes, that's what I thought of first and tried immediately. But it generates a syntax error. The problem is UNION. This works:

SELECT COUNT(*) FROM (SELECT ...)

where the subquery is simple. Use UNION in the subquery, though, and it chokes.

Is this possibly a SQL Server/T-SQL-specific problem? I can't find any clarification on MS's SQL Server Books Online, but they give no SELECT examples incorporating a subquery with UNION like this.

select count(*) from (select .......)
union
select count(*) from (select.....);

I appreciate the suggestions, but this returns two records, each containing the count for one of the SELECT statements. I need a single record containing the count for all the records in the UNION of the two statements. Again, I found a workaround, so it's not urgent. It's just bugging me, because it seems there ought to be a way to do this.

Incidentally, so as not to mislead anyone reading this, in my previous reply I was wrong about SELECT COUNT(*) FROM (SELECT ...). It also generates a syntax error. Maybe you just can't use the COUNT function with a subquery in any way, at least with SQL Server.

MapGeek,

Could you try aliasing the subquery:
SELECT COUNT(*) FROM (SELECT ...) as u

I am not a SQL Server user so I might be completely wrong in my guess.

Hope this helps.
Dimitar

Dimitar,

Yup, that did the trick, with both a plain subquery and a UNION in the subquery. Thanks very much for the help!

MapGeek

dUDE u saved my day
AD

MapGeek,

Try this and tell me is it solving your problem or not

select sum( counter)
from (select count(*) counter from Table1
Union
select count(*) from Table2
);

Sheetal

This works fine for MS SQL 2005

select sum(adet) from (
select count(*) as adet from (select * from Member ) as a1
union
select count(*) as adet from (select * from Member where recId=14) as a2
) as Toplam