SQL Server Invalid column name with alias and HAVING

ohtdti5x  于 2023-04-19  发布在  其他
关注(0)|答案(2)|浏览(110)

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?

8i9zcol2

8i9zcol21#

You can simply add the two columns like this:

SELECT COALESCE(col1, 0.0) + COALESCE(col, 0.0) AS total, id, email
FROM table
WHERE COALESCE(col1, 0.0) + COALESCE(col, 0.0) > 20

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.

wyyhbhjk

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?

SELECT tot.total, id, email
FROM table t
cross apply(values(COALESCE(col1, 0) + COALESCE(col, 0)))tot(Total)
where tot.Total > 20;

相关问题