count(*) of a union

Hello,

How does the count work if there is a union in the query?

I've tried this but it didn't work giving this error: Every derived table must have its own alias

select count(*)
from
(select cod from table1 UNION cod from table2)


I've also tried this but didn't work:

select count(*)
from
(select cod from table1 UNION select cod from table2)
as table


Thanks in advance
lulonAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
table is actually a reserved keyword
select count(*)
from
(select cod from table1 UNION select cod from table2)
as sq

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Lulon,

Both should work (depending upon the need to name the derived table).

Perhaps you want UNION ALL.  UNION will filter out the duplicate rows.


Good Luck,
Kent
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the second one should work, does that give the same error?
0
 
lulonAuthor Commented:
it gives this error:


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 4
0
 
lulonAuthor Commented:
yeah! you are right, thanks!
0
All Courses

From novice to tech pro — start learning today.