MySQL query with less than or greater than in the where statement using aggregate function?

Question

I have an MySQL query that is super simple but having an issue and wondering if someone can shed some light. All I am trying to do is include two aggregative functions that add "this year"+"last_year" at the same time filter out any results with less than 200 total_votes

Currently, the query works and the output looks like this:

name   | total_votes
--------------------
apple  |    119
lemon  |    218
orange |    201
pear   |    111

---

However when I add a where statement I get a syntax error:

 select 
        name,
        sum(this_year)+sum(last_year) as total_votes
        from fruit_sales
        group by name
        where total_votes>200

The above results in this syntax error in my SQL fiddle:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where total>200' at line 6"

I've also tried:

select 
name,
sum(this_year)+sum(last_year)>200 as total_votes
from fruit_sales
group by name

Here is an SQLfiddle with the table and my query in the works:

http://sqlfiddle.com/#!9/a6862/11

Any help here would be greatly appreciated!

Answer 1

SELECT
    name,
    SUM(this_year)+SUM(last_year) as total_votes
 FROM fruit_sales
 GROUP BY name
 HAVING SUM(this_year)+SUM(last_year) > 200

SqlFiddleDemo

You can also calculate sum as:

SELECT
    name,
    SUM(this_year + last_year) as total_votes
FROM fruit_sales
GROUP BY name
HAVING total_votes > 200;

SqlFiddleDemo2

For @lcm without HAVING and using subquery:

SELECT *
FROM (
SELECT
    name,
    SUM(this_year + last_year) as total_votes
 FROM fruit_sales
 GROUP BY name) AS sub
WHERE total_votes > 200;

SqlFiddleDemo3

Answer 2

You can also use the feldname from the select

select 
name,
sum(this_year)+sum(last_year) as total_votes
from fruit_sales

group by name
having total_votes>200;