PostgreSQL - Aliases column and HAVING
Other option is to surround query by WITH
statement - for example:
WITH x as (
SELECT coalesce(SUM(X.Count)*3600, 0) AS PJZ, X.Mass
FROM X
WHERE X.Mass > 2000
)
SELECT * from X WHERE PJZ >=10
It is far better then code duplication in my opinion
You can't use aliases in a having, and have to duplicate the statement in the having cluause. Since you only want to check for null, you could do this:
SELECT coalesce(SUM(X.Count)*3600, 0) AS PJZ, X.Mass
FROM X
WHERE X.Mass > 2000
HAVING ((X.Mass / coalesce(SUM(X.Count)*3600, 0) * 100) - 100) >= 10;
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)