Re: returning count(*) when it is > 1, else -1 - Mailing list pgsql-sql

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