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

Reply via email to