Re: Averages

AHH. I figured it out. I needed to use GROUP BY SELECT cartoons.reference, cartoons.cartoon_title, cartoons.date_added, IFNULL(AVG(ratings.rating), 0) as 'average_rating' FROM cartoons INNER JOIN ratings ON cartoons.reference = ratings.content_reference GROUP BY ratings.content_type, ratings.content_reference ORDER BY cartoons.cartoon_title ASC ----- Original Message ----- From: Ron Piggott To: php-db@lists.php.net Sent: Sunday, September 06, 2009 9:28 PM Subject: Averages I am making a voting / rating application. I am working on the following query: SELECT cartoons.reference, cartoons.cartoon_title, cartoons.date_added, IFNULL(AVG(ratings.rating), 0) as 'average_rating' FROM cartoons INNER JOIN ratings ON cartoons.reference = ratings.content_reference WHERE ratings.content_type =3 ORDER BY cartoons.cartoon_title ASC I only get 1 result because IFNULL(AVG(ratings.rating), 0) is giving me the result of all the ratings every submitted where ratings.content_type =3. The '3' is symbolic of cartoons. What I am wanting to do is show on the web page a list of all the cartoons and their current rating. In my ratings table I have a content_type column to know what area of the web site is being voted of (3 for cartoons) and I have content_reference which referrs to the auto_increment value of cartoons.reference. How do I be more specific with the AVG so I will get a rating for each cartoon in the table? Ron