Re: returning count(*) when it is > 1, else -1

Hello

2008/10/17 Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar>:
> Hi all. Im triyng to implement this in plain sql.
> The only thing i have working is
>
> select case when (select count(*) from test where id=$1 )
> > 0 then (select count(*) from test where id=$1)
> else -1
> end;
>
> But it does a doble count(*) that i must avoid.
> I cant refer to the 'first' count like
> select case when (select count(*) from test where id=$1 ) AS total
> > 0 then total
> else -1
> end;
>

you should to use subquery

select case when a.count > 0 then a.count else -1 from (select case
count(*) from test where id = $1) a;

regards
Pavel Stehule

> Because i have "Syntax error near AS"
>
> I have a plpgsql version of this, but i swear to my boss that it can be
> done is plain sql. Please tell me that im right :)
>
> Thanks!
> Gerardo
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>