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?

ntalbs · Accepted Answer · 2013-06-23 21:08:41Z

8

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;

answered Jun 23, 2013 at 21:08

ntalbs

29.6k8 gold badges72 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment