Column doesn't exist?

postgresql, sql

Solution

You can't reference a column alias in the WHERE clause.

  SELECT u1.id,
         COUNT(DISTINCT u2.userstatus) as TEMPCOLUMN 
    FROM USERS AS u1
    JOIN friendssym ON u1.id = friendssym.user_id
    JOIN USERS as u2 ON friendssym.friend_id = u2.id      
GROUP BY u1.id
  HAVING COUNT(DISTINCT u2.userstatus) = 1

In traditional SQL, the earliest you can reference a column alias is the `ORDER BY` clause. But MySQL and SQL Server allow access in the `HAVING` and `GROUP BY` clauses.

Problem

Was wondering if someone could help me out a little with this query: ``` SELECT u1.id,count(DISTINCT u2.userstatus) as TEMPCOLUMN FROM users AS u1 JOIN friendssym ON u1.id = friendssym.user_id JOIN (SELECT * FROM users) as u2 ON friendssym.friend_id=u2.id WHERE TEMPCOLUMN=1 group by u1.id; ``` I want to only have results where the count (which is renamed) is equal to 1. I get an error with this query: ``` ERROR: column "tempcolumn" does not exist ``` But the column should exist, right? Can anyone assist? Thanks!

Original source