Question: I am totally stuck on my homework. I am working with the Sakila sample db. I am supposed to write a query that will give me a list of all the customers who have rented >10 horror movies. This is what I have so far:

```
select CONCAT(c.first_name, " ", c.last_name) AS "Customer name", 
FROM customer AS c

INNER JOIN rental AS r
ON c.customer_id = r.customer_id

inner join  inventory as i
on i.inventory_id = r.inventory_id

inner join  film_category as fc
on i.film_id = fc.film_id
 inner join 
(select * from category as ca
where ca.name = 'horror') as h
 on h.category_id = fc.category_id 
 ;

```

I can get it to return **all** (846) of the customers who have rented horror movies, but how do I get only those who have rented more than 10 horror movies? I know I need the COUNT function, but when I put it in, it gives me 1 person, and a count of 846, which is a count of people, not horror movies rented. My logic is just failing me at this point. Help please!

Answer: 
Unfortunately I don’t know the sample database in question however I feel for the answer this isn't going to affect my response.

If you want to use COUNT you will need to also use [GROUP BY](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html) to achieve an aggregate count.

You could also look at [GOUP BY](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html) with a [HAVING](http://www.google.co.uk/url?sa=t&rct=j&q=mysql%20having&source=web&cd=1&cad=rja&ved=0CC8QFjAA&url=http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html&ei=VVs2UeraL8KYPceJgZgF&usg=AFQjCNFA7IECBXCwroBX9IcLvCxeD76e-A) clause.
In case someone need easy meal:

```
SELECT R.customer_id, COUNT(*) AS cnt 
FROM sakila.rental R LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id 
LEFT JOIN sakila.film F ON I.film_id = F.film_id 
LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id 
LEFT JOIN sakila.category C ON FC.category_id = C.category_id 
WHERE C.name = "Horror" 
GROUP BY R.customer_id HAVING cnt > 10

```