MySql order by count(*)

Question

The task is list of countries that has more than 10 customers I'm trying to show only the values that count > 10 in the table

I've tried this but its not filtering the table:

select co.country, count(*) from sakila.country as co
inner join city as ci on co.country_id = ci.country_id
inner join address as ad on ci.city_id = ad.city_id
inner join customer as cu on cu.address_id = ad.address_id
group by co.country
order by count(*) > 10;

Answer 1

By only showing the values with a count greater 10, you need to use having. having needs to be used, when you want to filter your results of a aggregate function, e.g. count, sum.

select co.country, count(*)
from sakila.country as co
inner join city as ci on co.country_id = ci.country_id
inner join address as ad on ci.city_id = ad.city_id
inner join customer as cu on cu.address_id = ad.address_id
group by co.country
having count(*) > 10;
order by count(*);

If you still want to order your results by the count, you still need to have the order by at the en