[SQL] returning count(*) when it is > 1, else -1
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; 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is > 1, else -1
Hello 2008/10/17 Gerardo Herzig <[EMAIL PROTECTED]>: > 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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is > 1, else -1
Gerardo Herzig wrote: > > 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; SELECT CASE WHEN total >0 THEN total ELSE -1 END AS new_total FROM ( SELECT count(*) AS total FROM test WHERE id=$1 ) AS raw_total -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is > 1, else -1
Richard Huxton wrote: > Gerardo Herzig wrote: >> 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; > > SELECT > CASE WHEN total >0 THEN total ELSE -1 END AS new_total > FROM ( > SELECT count(*) AS total FROM test WHERE id=$1 > ) AS raw_total > Pavel, Richard, you got it dudes! I have to say, that kinda 'reference before assingment' of "total" doesnt look logical to me. Thanks again!! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is > 1, else -1
Στις Friday 17 October 2008 15:11:10 ο/η Gerardo Herzig έγραψε: > Richard Huxton wrote: > > Gerardo Herzig wrote: > >> 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; > > > > SELECT > > CASE WHEN total >0 THEN total ELSE -1 END AS new_total > > FROM ( > > SELECT count(*) AS total FROM test WHERE id=$1 > > ) AS raw_total > > > Pavel, Richard, you got it dudes! I have to say, that kinda 'reference > before assingment' of "total" doesnt look logical to me. > Then, both you and your boss need some SQL courses :) (no offense) > Thanks again!! > Gerardo > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is > 1, else -1
Richard Huxton <[EMAIL PROTECTED]> writes: > SELECT > CASE WHEN total >0 THEN total ELSE -1 END AS new_total > FROM ( > SELECT count(*) AS total FROM test WHERE id=$1 > ) AS raw_total Actually you could just do SELECT CASE WHEN count(*) >0 THEN count(*) ELSE -1 END AS total FROM test WHERE id=$1; PG has avoided redundant calculations of duplicate aggregates for some time. (This doesn't help in the original formulation because it actually had two different sub-selects; the case that is handled is identical aggregate expressions within SELECT list or HAVING of a single SELECT.) regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is > 1, else -1
Achilleas Mantzios wrote: > Στις Friday 17 October 2008 15:11:10 ο/η Gerardo Herzig έγραψε: >> Richard Huxton wrote: >>> Gerardo Herzig wrote: 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; >>> SELECT >>> CASE WHEN total >0 THEN total ELSE -1 END AS new_total >>> FROM ( >>> SELECT count(*) AS total FROM test WHERE id=$1 >>> ) AS raw_total >>> >> Pavel, Richard, you got it dudes! I have to say, that kinda 'reference >> before assingment' of "total" doesnt look logical to me. >> > > Then, both you and your boss need some SQL courses :) (no offense) > Hahaha none taken dude, youre absolutely right. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is > 1, else -1
Tom Lane wrote: > Richard Huxton <[EMAIL PROTECTED]> writes: >> SELECT >> CASE WHEN total >0 THEN total ELSE -1 END AS new_total >> FROM ( >> SELECT count(*) AS total FROM test WHERE id=$1 >> ) AS raw_total > > Actually you could just do > > SELECT > CASE WHEN count(*) >0 THEN count(*) ELSE -1 END AS total > FROM test WHERE id=$1; > > PG has avoided redundant calculations of duplicate aggregates for some > time. (This doesn't help in the original formulation because it > actually had two different sub-selects; the case that is handled is > identical aggregate expressions within SELECT list or HAVING of a single > SELECT.) > > regards, tom lane > Thanks Tom! I like this one! It seems more readable to me. Thank you all! Yeah, even you Achilleas bastard (no ofense) :) Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
