I am adding a few columns and giving them alias. Then I want to display only the rows where the result of the addition is greater than certain amount:
SELECT (COALESCE(col1, 0.0) + COALESCE(col, 0.0)) as total, id, email
FROM table
HAVING total > 20
but I get Invalid column name 'total'
.
I thought I can use alias and HAVING
together, so why is it invalid?
2条答案
按热度按时间8i9zcol21#
You can simply add the two columns like this:
Aggregation (and therefore also the HAVING clause) is only required if you are adding values of different rows.
Note that the SELECT part is actually executed after the WHERE part. So, the WHERE part does not know the aliases given in the SELECT part and you must repeat the expression.
You could also use a sub-query to calculate the sum instead, but this seems to complicated for this simple case and can potentially slow down the query.
wyyhbhjk2#
You don't really need a sub query for this, you can simply repeat the expression in the where clause.
If repeating the expression is abhorrent you can
apply
it and re-use it.You mention addition but there is no valid addition in your example query (nor aggregation), although if there were the same applies, perhaps the following was your intention?