I have a fairly large mysql database and I would like to count all the equal values, to explain myself better:

I have a country field which has several countries values (USA, UK, MEX, etc..) and I want to get the number of countries without duplicate (if I have 100 USA and 2 UK and 1000 MEX the result would be 3).

The only way I could come up with is:

$i="0";
$query = "SELECT DISTINCT country FROM table"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$i++;
}
echo $i;

I really do think there are better ways to achieve this, maybe directly trought a sql statement?

As always thank you for your kind replies.

• php
• mysql

You've got mostly the right select statement, you just need to have MySQL do the counting for you:

SELECT COUNT(DISTINCT country)
FROM table

SELECT country, COUNT(country) FROM table GROUP BY country

SELECT country, COUNT(*) AS count
FROM table
GROUP BY country