mysql calculation results in WHERE clause

Question

I would like to select my results using calculations inside the query and use these results to compare inside the WHERE statement. But somehow that does not work. Guess you know why? Here is my code:

$statement = $pdo->prepare("SELECT * ,  `ft_lteam` -  `ht_lteam` AS  `ht2_lteam`,  
`ft_vteam` -  `ht_vteam` AS  `ht2_vteam` 
FROM  `sca` 
WHERE `ht2_lteam` > `ht2_vteam`");
$statement->execute(array('Max'));

Help would be great. Thanks for that!

"does not work" How? Does it give an error? Does, just, nothing happen? Does it lie in bed and watch TV all day? Anyway, this won't work: you need to either repeat the same expression in the `where` clause, or put it in a subquery or CTE with an alias name and then base your `select`/`where` off that. (Disclaimer: I don't know whether MySQL supports CTEs.) — underscore_d, Jul 13 '17 at 12:39
duplicate of [Using column alias in WHERE clause of MySQL query produces an error](https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — underscore_d, Jul 13 '17 at 12:39

score 2 · Accepted Answer · answered Jul 13 '17 at 12:41

You can use HAVING to filter on a computed column:

$statement = $pdo->prepare("SELECT * ,  `ft_lteam` -  `ht_lteam` AS  `ht2_lteam`,  
`ft_vteam` -  `ht_vteam` AS  `ht2_vteam` 
FROM  `sca` 
HAVING `ht2_lteam` > `ht2_vteam`");
$statement->execute(array('Max'));

Clear example here

SELECT col1,col2,col3,(col1*col2*col3) AS result, number FROM table
HAVING result > number
ORDER by result

mysql calculation results in WHERE clause

1 Answers1