PostgreSQL: case when using alias column

Question

I would like to do something like this:

select 
case when (select count(*) as score from users t1 )   >5   THEN score   else 0 end 

When i try it i get error:

column score doesn't exists. 

Can i do this in some other way? I need it to set a LIMIT value. I would like to do it of course in this way:

select 
case when (select count(*) as score from users t1 )   >5   THEN (select count(*) as score from users)    else 0 end 

but than I need execute two times this same query. Have someone some ideas?

Answer 1

You can use WITH clause:

with a as (select count(*) score from t)
select case when score > 5 then score else 0 end from a;

Or subquery (inline-view):

select case when score > 5 then score else 0 end 
from (select count(*) score from t) t;