21
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?

6EQUJ5HD209458b
  • 271
  • 1
  • 3
  • 12
  • 1
    [Why can't I use alias in a count(*)](http://stackoverflow.com/questions/2068682/why-cant-i-use-alias-in-a-count-column-and-reference-it-in-a-having-clause) – bartolo-otrit Nov 13 '15 at 17:21

3 Answers3

12

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;
jishi
  • 24,126
  • 6
  • 49
  • 75
9

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

Maciej
  • 10,423
  • 17
  • 64
  • 97
7

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)

  • But now I've got the problem with division by zero. PJZ could be zero. – 6EQUJ5HD209458b Sep 22 '11 at 08:10
  • Then exclude the rows WHERE PJZ is zero. Or depending on your requirements return 1 (or any other non-zero value) in the CASE statement –  Sep 22 '11 at 08:26
  • I can't exclude the rows, because I want to see them, too. If PJZ is 0 this: ((X.Mass / PJZ * 100) - 100) should show 100 for me. So I need another IF ELSE... – 6EQUJ5HD209458b Sep 22 '11 at 08:30