mysql use group by column in where condition

Question

How can I make this query work :

SELECT column1.....,SUM(Hits) AS Hits   
FROM table 
WHERE  SUM(Hits) > 100
GROUP BY column1.....

The problem is the where clause, mysql display error :

Error Code : 1111
Invalid use of group function

I try to change the query to :

 SELECT column1.....,SUM(Hits) AS Hits   
    FROM table 
    WHERE  Hits > 100
    GROUP BY column1.....

It did not help.

thanks

Answer 1

SELECT column1.....,SUM(Hits) AS HitsSum 
FROM table 
GROUP BY column1.....
HAVING HitsSum > 100

Answer 2

SELECT column1.....,SUM(Hits) AS Sum_Hits   
FROM table 
GROUP BY column1.....
HAVING Sum_Hits > 100

Answer 3

The reason for the error is that you can not use aggregate functions, or column aliases to derived columns using aggregate functions, in the WHERE clause. These can only be used in the HAVING clause, which requires defining a GROUP BY clause (if it doesn't already exist).

I don't recommend using the column alias in GROUP BY or HAVING clauses - there's a risk that the query will not be portable to other databases. SQL Server is the only other database that I'm aware of that supports column aliases in the GROUP BY or HAVING clauses.

  SELECT t.column1....., SUM(t.hits) AS HitsSum 
    FROM TABLE t
GROUP BY t.column1.....
  HAVING SUM(t.hits) > 100