CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Ashley McVeigh
Ashley McVeigh

Reputation: 35

mysql how to query where ROUND(AVG(`row`)) equals

I have a query which selects a bunch of "items" and an average rating given by customers for that item. it looks like this:

SELECT `items`.`itemid`,
       `items`.`name`,
       `items`.`address`,
       `items`.`suburb`,
       `items`.`latitude`,
       `items`.`longitude`,
       `reviews`.`comment`,
       Round(Avg(`reviews`.`rating`), 0) AS avg
FROM   `items`
       RIGHT JOIN `reviews`
               ON `items`.`itemid` = `reviews`.`itemid`
GROUP  BY `items`.`itemid`;

now, this works and i get all the information about an item and its average rating.

what I want, is to add a

WHERE avg ='3';

where that 3 is a selected value so i can search "all items with an average rating of 3" etc.

where avg ='3' doesn't work. i was just wondering if there is a way to do this? using PHP even.

Upvotes: 0

Views: 563

Answers (1)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

Use having clause to filter results from aggregate function 

SELECT `items`.`itemid`,
       `items`.`name`,
       `items`.`address`,
       `items`.`suburb`,
       `items`.`latitude`,
       `items`.`longitude`,
       `reviews`.`comment`,
       Round(Avg(`reviews`.`rating`), 0) AS avg
FROM   `items`
       RIGHT JOIN `reviews` using (`itemid`)
GROUP  BY `reviews`.`itemid`
HAVING Round(Avg(`reviews`.`rating`), 0) = 3

Demo using SQL Fiddle

Upvotes: 1

Related Questions