Counting duplicate rows on two tables?

[Adamthenewbie]

I want to count the amount of duplicate rows in two tables with one SQL query and wondering if this is possible. Here are the two queries:

select city, COUNT(city) as TOTALFOUND FROM SearchHistory GROUP BY city ORDER BY TOTALFOUND DESC LIMIT 0,2

This returns something like:
city TOTALFOUND
Ottawa 23
Toronto 15

Then I have another table with the exact same table structure and can get what I want with this:

select city, COUNT(city) as TOTALFOUND FROM MessagesSent GROUP BY city ORDER BY TOTALFOUND DESC LIMIT 0,2

This returns something like:
city TOTALFOUND
Ottawa 80
Toronto 45

I somehow want to have one query where the result would be:

This returns something like:
city TOTALFOUND
Ottawa 103
Toronto 60

Is this possible?

[toma42]

use a UNION

p.s. don't use all caps for aliases. all columns, tables, and aliases should be lower case. Use upper case for database reserved words like DESC, LIKE, NOT, and UNION

[Adamthenewbie]

Thanks for the info. I am getting closer but still can't get exactly what I want. I have now:

SELECT city, COUNT(city) AS totalfound FROM SearchHistory  WHERE city != '' GROUP BY city 
UNION
SELECT city, COUNT(city) AS totalfound FROM MessagesSent WHERE city != '' GROUP BY city 
ORDER BY totalfound DESC LIMIT 0,4

The problem instead of returning:
Ottawa 103
Toronto 60

it returns:
Ottawa 80
Toronto 45
Ottawa 23
Toronto 15

Is there a way to "GROUP BY" city using a UNION? I can't seem to figure it out tried a bunch of things... Thanks again for the help!

[Lars_Berg]

select city, 
       count(*) as totalfound 
  from (select city 
          from SearchHistory 
         union all
        select city 
          from MessagesSent) dt
 group by city 
 order by totalfound desc
 limit 0,2

[Adamthenewbie]

Thanks so much that did the trick!

Adam