Skip site navigation (1) Skip section navigation (2)

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

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Gerardo Herzig" <gherzig(at)fmed(dot)uba(dot)ar>
Cc: "postgres list" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: returning count(*) when it is > 1, else -1
Date: 2008-10-17 12:00:55
Message-ID: 162867790810170500l320a1a28vfa9bdba1839527a9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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
>

In response to

pgsql-sql by date

Next:From: Richard HuxtonDate: 2008-10-17 12:01:14
Subject: Re: returning count(*) when it is > 1, else -1
Previous:From: Gerardo HerzigDate: 2008-10-17 11:45:23
Subject: returning count(*) when it is > 1, else -1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group