postgresqlhavingcolumn-alias

PostgreSQL - Aliases column and HAVING


SELECT  
CASE WHEN SUM(X.Count)*3600 is null THEN  '0'  
            ELSE  
            SUM(X.Count)*3600  
       END AS PJZ,  
       X.Mass  
FROM X  
WHERE X.Mass > 2000  
HAVING ((X.Mass / PJZ * 100) - 100) >= 10;

Getting: ERROR: Column »pjz« doesn't exists.

How can I do something like this?


Solution

  • Wrap it into a derived table:

    SELECT CASE 
              WHEN PJZ = 0 THEN 100
              ELSE PJZ
           END as PJZ,
           mass
    FROM (
        SELECT CASE 
                 WHEN SUM(X.Count)*3600 is null THEN '0'  
                 ELSE SUM(X.Count)*3600  
               END AS PJZ,  
               X.Mass  
        FROM X  
        WHERE X.Mass > 2000  
        GROUP BY X.mass
    ) t
    WHERE PJZ = 0 
       OR ((X.Mass / PJZ * 100) - 100) >= 10;
    

    (Note that I added the missing group by as otherwise the query would not be valid)