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.
You can write a query without left join/inner join as well. Note that there is no customer that has more than 10 horror rentals, so having a blank result should not confuse you (Try with 2 and it works!)

```
SELECT cu.first_name, cu.last_name, COUNT(r.rental_id) as horror_film_count
FROM customer AS cu, rental AS r, inventory as i, film as f, category as ca, 
film_category as fc
WHERE cu.customer_id = r.customer_id 
AND r.inventory_id = i.inventory_id
AND i.film_id = f.film_id
AND f.film_id = fc.film_id
AND fc.category_id = ca.category_id
AND ca.category_id = 11
GROUP BY cu.customer_id
HAVING horror_film_count > 10

```