SQL - WHERE Condition on SUM()

Question

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

Solution 1

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()

Solution 2

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)

Solution 3

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.

Comments

[user1766760]

But aren't you still running into the issue where rating is an invalid identifier?

[Adam Wenger]

@Francesco: Sorry, I needed to use the HAVING instead of the WHERE clause - I updated my answer

[Felypp Oliveira]

As from MySQL the HAVING clause applies to this case as expected. I just found that on standard SQL the HAVING clause must refer to some named column in GROUP BY. I don't know if other RDBMS works like MySQL in this case...

[user1766760]

Gotcha, didn't notice the MySQL part (hey I learned something :) btw, if you were curious, it doesn't work in Oracle)

[Adam Wenger]

Interesting, I did not know that about MySQL either.

[Adam Wenger]

@user1766760 You didn't miss the tag, MySQL wasn't there initially. I added it once I saw the error number the OP posted to my initial answer.

[Felypp Oliveira]

@user1766760 Yeah, I was curious about this... haha! Nice to know! In some cases I do like this as I'm actually working just with MySQL. This extension can also be disabled and so my solution will not work... =Z

[Adam Wenger]

@Francesco Sure, what would you like to sort by? Just put your ORDER BY after the HAVING