ironmaiden666 ironmaiden666 - 9 months ago 33
MySQL Question

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

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?

Answer Source

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