SQL-SUM()的条件 [英] SQL - WHERE Condition on SUM()

问题描述

是否可以执行以下操作:

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

推荐答案

这可以通过HAVING子句完成:

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

注意:添加了放置ORDER BY语句的位置

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.

正如@jagra的答案中提到的那样，您应该能够使用AVG()而不是SUM() / COUNT()

As was mentioned in @jagra's answer, you should be able to use AVG() instead of SUM() / COUNT()

这篇关于SQL-SUM()的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！