MYSQL Sakila Database - Find a list of customers who have not rented a movie yet

Question

I'm learning SQL for the first time in a university course, and we're using the popular Sakila Database. I'm stuck with the question - Find a list of customers who have not rented a movie yet. So far my thought process is something like this:

#List of customers who have not rented a movie yet
SELECT
    concat(c.first_name, " ", c.last_name), count(r.rental_id) as "Number of Rentals"
FROM
    customer c, rental r
WHERE
    c.customer_id = r.customer_id
    AND count(r.rental_id) is NULL
GROUP BY
    c.customer_id;

However, I keep getting the error "Invalid Use of Group Function" and I can't seem to figure out why. Can someone please help me?

You don't need any counting or grouping. You just need to think about the different types of joins. Does that help? — Strawberry, Mar 12 '17 at 08:51
Possible duplicate of [MySQL: Invalid use of group function](http://stackoverflow.com/questions/2330840/mysql-invalid-use-of-group-function) — Shuo, Mar 12 '17 at 10:13

score 0 · Accepted Answer · answered Mar 12 '17 at 06:16

0

Try changing the WHERE statement to Having statement, like this:

SELECT
    concat(c.first_name, " ", c.last_name), count(r.rental_id) as "Number of Rentals"
FROM
    customer c, rental r
WHERE 
    c.customer_id = r.customer_id
GROUP BY
    c.customer_id
HAVING count(r.rental_id) is NULL

answered Mar 12 '17 at 06:16

Koby Douek

16,156
19
74
103

Thanks! That helped! – ironmaiden666 Mar 12 '17 at 06:35

MYSQL Sakila Database - Find a list of customers who have not rented a movie yet

1 Answers1