AVG/COUNT MySQL query NOT working

Question

I am not very experienced in MySQL queries so I might be doing something wrong. Simplified my query is like this:

SELECT item.*, AVG(itemRating.rating) as 'rating', COUNT(itemRating.rating) as 'ratingCount' 
  FROM item, itemRating 
 WHERE item.id IN (...) 
   AND itemRating.item_fk = item.id 
 GROUP BY itemRating.item_fk

It works fine, except for when an item has no rating (no record in the itemRating table). Is there any way I can solve this without losing information?

You write: SELECT item.*, AVG(itemRating.rating) as 'rating' ... GROUP BY itemRating.item_fk It seems to me that you need to write either SELECT itemRating.item_fk, AVG(itemRating.rating) as 'rating' ... GROUP BY itemRating.item_fk or SELECT item.*, AVG(itemRating.rating) as 'rating' ... GROUP BY item.* (You can't write GROUP BY item.* I did that because I don't know what columns you have in the item table, you need to specify every column of the item in the GROUP BY clause) — neshkeev
– neshkeev, Commented Oct 10, 2014 at 9:17
It seems better to write GROUP BY item.id than GROUP BY itemRating.item_fk, you are right. itemRating.item_fk is a fk=foreign key linking to item.id, so the query will give no problems. — sprunk
– sprunk, Commented Oct 13, 2014 at 7:27
You didn't even understand what I meant, I guess you need to read more about group by and how to use it. — neshkeev
– neshkeev, Commented Oct 13, 2014 at 7:31

juergen d · Accepted Answer · 2014-10-10 08:30:55Z

1

SELECT item.id, 
       AVG(itemRating.rating) as 'rating', 
       COUNT(itemRating.rating) as 'ratingCount' 
  FROM item
  LEFT JOIN itemRating ON itemRating.item_fk = item.id 
 WHERE item.id IN (...) 
 GROUP BY item.id

answered Oct 10, 2014 at 8:30

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sprunk Over a year ago

Works like a charm. I think I should update my MySQL-basics :)

Collectives™ on Stack Overflow

AVG/COUNT MySQL query NOT working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related