Country-wise Analysis of Movies

Write a query to find the number of movies rented across each country. Display only those countries where at least one movie was rented. Arrange these countries in the alphabetical order.

DB: https://dev.mysql.com/doc/sakila/en/sakila-structure.html

Below is the written code

select e.country, count(a.rental_id) as rental_count
from rental a
left outer join customer b ON   (a.customer_id=b.customer_id)
left outer join address c ON    (b.address_id=c.address_id)
left outer join city d ON       (c.city_id=d.city_id)
left outer join country e ON    (d.country_id=e.country_id)
group by country 
order by rental_count >=1

my output is displayed as below

*************************** 1. row ***************************
Angola
4
*************************** 2. row ***************************
American Samoa
4
*************************** 3. row ***************************
Afghanistan
4
*************************** 4. row ***************************
Algeria

2
*************************** 5. row ***************************
New Zealand
1

How do i convert the output to Alphabetical Order

Answer

You are probably confusing ORDER BY and HAVING.

Display only those countries where at least one movie was rented.

And this is how you try to do it:

order by rental_count >=1

But to filter on aggregated columns you should use the HAVING clause:

having rental_count >=1

However – You can achieve the same by using INNER JOIN instead of LEFT OUTER JOIN. Then no HAVING clause is needed in this case.

The ORDER BY clause is used to get the result in a specific order. If you want the countries to be sorted alphabetically, you would use

order by e.country