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;
>Solution :
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