[SOLVED] Count from two tables with an UNION?

[agge]

I got a problem when I do a count from two tables and when I have an UNION between them. MY select looks like this:

SELECT DISTINCT COUNT(ID) AS n FROM Table1 WHERE age = 10 UNION
SELECT COUNT(ID) AS n FROM Table2 WHERE age = 10

This give me a table with two rows.

But what I want is one row which has counted this two tables and sorted out all dubletts so I only get the DISTINCT values.. ???

[btherl]

It depends on which database version you are using, but try this:

 

SELECT count(*) FROM (select id from Table1 where age = 10 UNION select id from Table2 where age = 10)

 

It might ask you to give an alias for the subquery.. if so, add "AS foo" at the end of the query.  Or it may give an error if you are using mysql version 4.

 

There is no need to use distinct, as UNION already does that.  If you don't want distinct values you should use UNION ALL instead.