Can column aliases be evaluated in the WHERE clause?

Question

First off, I'm not an expert with MySQL queries, and I can't seem to find a solution to my problem.

Example:

SELECT SUM(IF(table1.col1<4,1,0)) AS score WHERE score>0 GROUP BY table1.col2

The above would give an error as the column score does not exist in table1.

This is my workaround:

SELECT SUM(IF(table1.col1<4,1,0)) AS score GROUP BY table1.col2

Then checking each row in PHP

if($row['score']>0){...

There is a performance issue as I'm looping through rows that I know I will not need, ideally I need to ignore those rows within the query.

My understanding is that the WHERE clause is triggered for each row of the table, not the grouped row.

Is there a way to do this?

Answer 1

Having support column aliases, so you can do this:

SELECT SUM(IF(table1.col1<4,1,0)) AS score
GROUP BY table1.col2
HAVING score > 0

and you can simplify it as you're trying to count rows where col1 < 4:

SELECT COUNT(table1.col1) AS score
 WHERE table1.col1 < 4
GROUP BY table1.col2
HAVING score > 0

Answer 2

The WHERE clause is evaluated before rows are grouped according to the GROUP BY clause; hence it's not only a problem in your syntax: the score column is simply meaningless in this context. It can only be computed after groups have been evaluated, hence your solution is definitely HAVING.