Counting countries once...help?

[aymie]

I'm trying to union all my tables and group by countries using something like this:

 

$query = "SELECT DISTINCT(`country`) FROM (SELECT COUNT(DISTINCT(`country`) FROM $table UNION ALL) AS `total` GROUP BY `country`";

 

Because I have 25 member tables in my database and I keep adding tables (each table is a different subject) and 951 members in total from 220 countries (including repeated countries) BUT it's ACTUALLY 60 different countries without repeats (I went through and counted manually).

 

HOWEVER, mysql is counting repeated countries e.g. USA 30 times when I only want the repeating countries to be counted ONCE.

 

How do I get the countries to be counted only once? The query example above is giving me 0 and when I write it any other way it gives me 220.

I came to that code from reading MANY tutorials of how to select something within a select and unioning all tables to return a result, but it's not doing what I want it to do.

 

Can someone please help me?

 

[btherl]

Try working on your subquery first.  Can you post the entire query?  It doesn't matter if it's long.  Once your subquery works properly, then you can add either GROUP BY or DISTINCT to remove the duplicates.  Something like this ought to work:

 

SELECT country FROM
  ( SELECT country FROM t1 UNION ALL
    SELECT country FROM t2 )
AS all_countries
GROUP BY country

 

The subquery will return all the countries, and then the GROUP BY will remove duplicates.  You could replace the GROUP BY with a DISTINCT at the start for the same effect.