Thread: 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
Hello 2008/10/17 Gerardo Herzig <gherzig@fmed.uba.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
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
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
Στις 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
Richard Huxton <dev@archonet.com> 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
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.
Tom Lane wrote: > Richard Huxton <dev@archonet.com> 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