Averages

------=_NextPart_000_0008_01CA2F38.F202E330
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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` =3D `ratings`.`content_reference` WHERE =
`ratings`.`content_type` =3D3 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` =3D3. The '3' is symbolic of cartoons. =20

What I am wanting to do is show on the web page a list of all the =
cartoons and their current rating. =20

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
------=_NextPart_000_0008_01CA2F38.F202E330--

Re: Averages

------=_NextPart_000_0022_01CA2F3B.A62B9350
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

AHH. I figured it out. I needed to use GROUP BY=20

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` =3D `ratings`.`content_reference` GROUP BY =
`ratings`.`content_type`, `ratings`.`content_reference` ORDER BY =
`cartoons`.`cartoon_title` ASC
----- Original Message -----=20
From: Ron Piggott=20
To: php-db@lists.php.net=20
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` =3D `ratings`.`content_reference` WHERE =
`ratings`.`content_type` =3D3 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` =3D3. The '3' is symbolic of cartoons. =20

What I am wanting to do is show on the web page a list of all the =
cartoons and their current rating. =20

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
------=_NextPart_000_0022_01CA2F3B.A62B9350--