How do I check if a column have more than two items and if it have than do the avg() on it in SQL?

I have 4 tables

•   user(id, name)
•   item(id, description, category, price)
•   buys(user, item, price)
•   rates(user, item, rating)

The user and item from buys and rates are foreign keys. I can`t figure out how to put a condition to take out the items which have only one rating and do the average only on the ones who have 2 or more ratings.

This is the question in the exercises (For each item that has at least two ratings, find the average rating for the item.)

This is my code.

 SELECT count(distinct rating) as rating, avg(rating) as average
    from rates
    where rates.rating is not null
    group by item

Answer

You can check if the item is presented in the table more than once with HAVING condition:

SELECT COUNT(DISTINCT rating) AS rating, AVG(rating) AS average
FROM rates
WHERE rates.rating IS NOT NULL
GROUP BY item
HAVING COUNT(rates.item) >= 2

Take a look here: https://www.postgresqltutorial.com/postgresql-having/