using the variable from the case clause in "where" statment-CodePudding

In my SQL query I have a CASE clause. How can I use the variable name from the CASE in where statment. This is my query

SElECT 
    a,
    b,

    CASE 
        WHEN AVG(CAST([x] as DECIMAL(9,2))) = 0 
        THEN 0 
        ELSE AVG(CAST([y] as DECIMAL(9,2))) /  AVG(CAST([x] as DECIMAL(9,2))) 
    END AS z

FROM magic_table
WHERE  z > 0
GROUP BY a,b

here I can not grab the z in where clause. But How can I do it ? is there any other way of doing it ?

CodePudding user response：

As Panagiotis Kanavos already commented you cant use column alias in where statement but you can put your whole case statement in HAVING clause like so:

SElECT 
    a,
    b,
    CASE 
        WHEN AVG(CAST([x] as DECIMAL(9,2))) = 0 
        THEN 0 
        ELSE AVG(CAST([y] as DECIMAL(9,2))) /  AVG(CAST([x] as DECIMAL(9,2))) 
    END AS z

FROM magic_table
GROUP BY a,b
HAVING CASE 
        WHEN AVG(CAST([x] as DECIMAL(9,2))) = 0 
        THEN 0 
        ELSE AVG(CAST([y] as DECIMAL(9,2))) /  AVG(CAST([x] as DECIMAL(9,2))) 
    END > 0