how to call an alias in SQL postgres

Question

I currently have:

sum(CASE 
        when co.date between '1/1/2022' and now()
        then co.amount
        else 0
        End) as RevYTD,
        
sum(CASE
    WHEN co.date between '1/1/2021' and '12/31/2021'
    THEN co.amount
  END) AS Rev2021,

and am trying to have something that does but am running into errors saying 'revYTD does not exist':

 (case
 WHEN RevYTD > Rev2021
 THEN "Higher"
 END) as "RevenueStatus"

Any help would be greatly appreciated!

Answer 1

Column aliases can be used with GROUP BY and ORDER BY clauses. We cannot use a column alias with WHERE and HAVING clauses. If you want to use aliases with WHERE clause, you must write subquery. For example:

-- incorrect syntax:
select a, b, a+b as c from table where c > 10;

-- correct syntax: 
select * from (
select a, b, a+b as c from table
) tbl where tbl.c > 10;