Subject: Re: CASE
From: Rod Taylor <rbt@rbt.ca>
To: jtx@hatesville.com
Cc: pgsql-sql@postgresql.org
In-Reply-To: <002201c31dd4$3f34c100$0000fea9@theplague>
References: <002201c31dd4$3f34c100$0000fea9@theplague>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-x6SPYLWv4yFJUehA1nSZ"
Organization: 
Message-Id: <1053386689.24151.49.camel@jester>
Mime-Version: 1.0
Date: 19 May 2003 19:24:49 -0400

--=-x6SPYLWv4yFJUehA1nSZ
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Mon, 2003-05-19 at 02:59, James Taylor wrote:
> Hi everyone, I'm trying to figure out how to get CASE working in a sum
> aggregate function.  Pretty simple query:
>=20
> select sum(numbers) from lists where uid=3D1;
>=20
> It's possible that there may be 0 rows where uid =3D=3D 1.  Instead of
> returning 1 blank row, I want it to return '0'.  So, I tried this:
>=20
> select case(sum(numbers)) when null then 0 else sum(numbers) end from
> list_results;

Use Coalesce:

SELECT coalesce(sum(numbers), 0) FROM lists WHERE uid =3D 1;

To use case, efficiently, if you really want:

SELECT CASE WHEN sum_numbers IS NULL THEN 0
            ELSE sum_numbers
       END AS sum_numbers
  FROM (SELECT sum(numbers) AS sum_numbers
          FROM lists
         WHERE uid =3D 1
       ) as tab;


--=20
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-x6SPYLWv4yFJUehA1nSZ
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA+yWfA6DETLow6vwwRAht7AJ9N7kFauyQMt1DvB4uLLfX8hhHknQCfamQX
QqjfSsv8KMF2qr6ubKsvJxM=
=H6tI
-----END PGP SIGNATURE-----

--=-x6SPYLWv4yFJUehA1nSZ--