SELECT DISTINCT ... but still get how many of each?

[DrDoc]

Is there a way I can accomplish this in one query: Get all unique in a column, but also get how many of each?

Right now I'm using SELECT DISTINCT to get the unique matches, only to query the database again to see how many of each there are.

There are about 1800 rows (and growing day by day)... About 15 unique (not growing too much)...

[figment88]

I think you need to use SELECT varname, COUNT(varname) Group by varname

where varname is name of the column you want to get the count of.

[DrDoc]

But see, that would count how many I have total... not individually.

Ok, the column is a list of countries. I only want to get unique countries (if USA is listed more than once, it should still only be displayed once)... which is why I'm using DISTINCT. But, I still want to know how many times each country appears.

The output is something like this:

Andorra (2)
Belgium (14)
Canada (53)
Denmark (28)
...

[daisho]

The DISTINCT keyword applied to the _ENTIRE_ row and not just the column you think your applying it to.

I'm sure each row is distinct even if some of the selected values match.

You will want to group by country.

daisho.

[DrDoc]

I know... and this is what I'm doing:

SELECT DISTINCT country FROM table ORDER BY country ASC

The problem isn't how to get a list of all unique countries. The problem is how to figure out how many of each, without having to perform extra queries.

$result = mysql_query("SELECT DISTINCT country FROM table ORDER BY country ASC");
while($get = mysql_fetch_row($result)) {
mysql_query("SELECT * FROM $mysql_profile_table WHERE country='".$get[0]."'");
$find = mysql_num_rows($lookup);
echo $get[0]." (".$find.")<br>";
}

See? I don't want to do it that way... I want to know if there's an easier way of doing it, preferably in one query.

[daisho]

SELECT country, count(*) as country_count FROM table GROUP BY country ORDER BY country ASC

That should do it.

daisho.

[figment88]

DrDoc, I gave you a snippet. Daisho was nice enough to give you a full blown sql statement. You keep missing the important point - you need a "GROUP BY" clause.

[DrDoc]

Oh... I see. Didn't get that at first... Thanks, both of you! :)