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!

in most RDBMS you can't reference a column alias at the same time. You could create a CTE which materializes teh result for RevYTD first and then haave a query which generates RevenueStatus using the RevYTD... as a new column. or you could create an inline view with everything but RevenueStatus and add it in an outer query. — xQbert, Oct 12 '22 at 16:30
I say "Most" because well, you can in mySQL. you just have to wrap the case you need the alias from in it's own set of ()'s and ensure it's use follows it in the select statement... but it's not a feature I'd trust. — xQbert, Oct 12 '22 at 21:05

score 0 · Answer 1 · answered Oct 12 '22 at 16:37

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;

how to call an alias in SQL postgres

1 Answers1