2

I need to count rows in more than one table in SQL Server 2008. I do this:

select count(*) from (select * from tbl1 union all select * from tbl2)

But it gives me an error of incorrect syntax near ). Why?

PS. The actual number of tables can be more than 2.

hgulyan
  • 8,099
  • 8
  • 50
  • 75
c00000fd
  • 20,994
  • 29
  • 177
  • 400

4 Answers4

3

try this:

You have to give a name to your derived table

select count(*) from 
(select * from tbl1 union all select * from tbl2)a
Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
3

In case you have different number of columns in your tables try this way

 SELECT count(*) 
 FROM (
      SELECT NULL as columnName 
      FROM tbl1 
          UNION ALL
      SELECT NULL 
      FROM tbl2
     ) T
hgulyan
  • 8,099
  • 8
  • 50
  • 75
  • Good point. Thanks. Can you explain though, what does "NULL a" stand for? – c00000fd Sep 06 '12 at 10:37
  • It doesn't matter what you select from tables if you need only count of rows, that's why it can be just a 'NULL' value and a is alias for that column. To make it clearer I wrote 'NULL as a' – hgulyan Sep 06 '12 at 10:39
  • Oh, so I can technically drop "as a" and just do "select NULL from tbl1", right? – c00000fd Sep 06 '12 at 10:44
  • right and you'll get all rows without values, but it's enough for counting. – hgulyan Sep 06 '12 at 10:46
1

I think you have to alias the SELECT in the FROM clause:

select count(*) 
from 
(
   select * from tbl1 
   union all 
   select * from tbl2
) AS SUB

You also need to ensure that the * in both tables tbl1 and tbl2 return exactly the same number of columns and they have to be matched in their type.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
0

I don't like doing the union before doing the count. It gives the SQL optimizer an opportunithy to choose to do more work.

AlexK's (deleted) solution is fine. You could also do:

select (select count(*) from tbl1) + (select count(*) from tbl2) as cnt
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hah, I didn't think about this simple one. Just from curiosity, you put "as cnt" after the second SELECT, was that the intention? Or, do I need parenths there? – c00000fd Sep 06 '12 at 18:47
  • Also since you brought up efficiency, two questions. 1. Would it help to replace "count(*)" with "NULL as columnName" as was suggested by hgulyan. 2. And, is there a resource/document that can be used for tSQL efficiency/speed optimization? – c00000fd Sep 06 '12 at 18:49
  • If you want to do this efficiently, then don't scan the table at all . . . . http://stackoverflow.com/questions/5199412/number-of-rows-sql-server. My understanding is that SQL Server will use indexes (if available) for count(*), so the execution performance should be the same as count(1). – Gordon Linoff Sep 06 '12 at 19:32