What is the best practice to count equal values in a database?

Go To StackoverFlow.com

0

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.

2009-06-16 13:54
by 0plus1


8

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
2009-06-16 14:01
by Welbog
This is my 200th answer, too. Go me - Welbog 2009-06-16 14:06


1

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

2009-06-16 14:05
by Peter Ruderman


0

SELECT country, COUNT(*) AS count
FROM table
GROUP BY country
2009-06-16 14:05
by RedFilter
Ads