Reputation: 5664
Is it possible to do something like this:
SELECT
`e`.*,
`rt`.`review_id`,
(SUM(vt.percent) / COUNT(vt.percent)) AS rating
FROM `catalog_product_entity` AS `e`
INNER JOIN `rating_option_vote` AS `vt`
ON vt.review_id = e.review_id
WHERE (rating >= '0')
GROUP BY `vt`.`review_id`
In particular I would like to put a where condition on the division result value
Upvotes: 4
Views: 17282
Reputation: 17540
This can be accomplished with a HAVING clause:
SELECT e.*, rt.review_id, (SUM(vt.percent) / COUNT(vt.percent)) AS rating
FROM catalog_product_entity AS e
INNER JOIN rating_option_vote AS vt ON e.review_id = vt.review_id
GROUP BY vt.review_id
HAVING (SUM(vt.percent) / COUNT(vt.percent)) >= 0
ORDER BY (SUM(vt.percent) / COUNT(vt.percent)) ASC
Note: Added where to put ORDER BY
statement
The query optimizer should also not calculate the Average multiple times either, so that should not be a concern here.
As was mentioned in @jagra's answer, you should be able to use AVG()
instead of SUM() / COUNT()
Upvotes: 7
Reputation: 2187
You can use HAVING
clause:
SELECT
`e`.*,
`rt`.`review_id`,
(SUM(vt.percent) / COUNT(vt.percent)) AS rating
FROM `catalog_product_entity` AS `e`
INNER JOIN `rating_option_vote` AS `vt`
ON vt.review_id = e.review_id
GROUP BY `vt`.`review_id`
HAVING rating >= 0;
As your question is tagged MySQL this solution should work, as the documentation shows:
...In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the HAVING clause that are not named in the GROUP BY clause. A MySQL extension permits references to such columns to simplify calculations. ...
This extension is enabled by default on official MySQL compilation.
Reference: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html (on the first occurrence of the word HAVING
on the page)
Upvotes: 3
Reputation: 553
You need to use HAVING clause when filtering by aggregate functions:
SELECT
# `e`.*, => is this needed
`rt`.`review_id`,
(SUM(vt.percent) / COUNT(vt.percent)) AS rating
AVG(vt.percent) AS rating1 # same as above
FROM `catalog_product_entity` AS `e`
INNER JOIN `rating_option_vote` AS `vt`
ON vt.review_id = e.review_id
GROUP BY `vt`.`review_id`
HAVING AVG(vt.percent) > 0
Two other notes:
1) SUM(x)/COUNT(x) <=> AVG(x)
2) You are including e.* in select but not in group by. MySql lets you do it but other databases wont.
Upvotes: 1
Reputation: 1148
Use HAVING
for aggregate conditions (such as the one you have)
SELECT `e`.*, `rt`.`review_id`, (SUM(vt.percent) / COUNT(vt.percent)) AS rating
FROM `catalog_product_entity` AS `e`
INNER JOIN `rating_option_vote` AS `vt`
ON vt.review_id = e.review_id
WHERE ( rating >= '0')
GROUP BY `vt`.`review_id'
HAVING (SUM(vt.percent) / COUNT(vt.percent)) > 0
Upvotes: 0