Sql throwing up error on aggregate in where clause

Question

SELECT A.A1, A.B2, A.C2, 
(select AVG(X1.parm) 
 from (VALUES (A.KK1),(A.KK2)) as X1(parm) 
 where X1.parm >110 and X1.parm is not NULL
) as observed           
FROM Table A
WHERE observed > 200

This is throwing up error: Invalid column name 'observed' why?

Answer 1

You can't reuse an alias defined in the select clause in the where clause in the same scope.

A workaround here is a lateral join:

select a.a1, a.b2, a.c2, x.observed
from table a
cross apply (
    select avg(x.parm) observed
    from (values (a.kk1),(a.kk2)) as x(parm) 
    where x.parm > 110 and x.parm is not null
) x
where x.observed > 200