X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by developer.postgresql.org (Postfix) with ESMTP id 3F55592351C
	for <pgsql-sql@postgresql.org>; Mon, 19 May 2003 20:45:15 -0400 (EDT)
Received: from developer.postgresql.org ([64.117.224.193])
	by localhost (developer.postgresql.org [64.117.224.193:10024])
	(amavisd-new) with ESMTP id 96371-09 for <pgsql-sql@postgresql.org>;
	Mon, 19 May 2003 20:45:12 -0400 (EDT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by developer.postgresql.org (Postfix) with ESMTP id 7E9F6924D1D
	for <pgsql-sql@postgresql.org>; Mon, 19 May 2003 18:49:22 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id E07E4D645; Mon, 19 May 2003 15:49:21 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id D62475C0B; Mon, 19 May 2003 15:49:21 -0700 (PDT)
Date: Mon, 19 May 2003 15:49:21 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: James Taylor <jtx@hatesville.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: CASE
In-Reply-To: <002201c31dd4$3f34c100$0000fea9@theplague>
Message-ID: <20030519154658.Q43251-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/373
X-Sequence-Number: 13538


On Sun, 18 May 2003, James Taylor wrote:

> Hi everyone, I'm trying to figure out how to get CASE working in a sum
> aggregate function.  Pretty simple query:
>
> select sum(numbers) from lists where uid=1;
>
> It's possible that there may be 0 rows where uid == 1.  Instead of
> returning 1 blank row, I want it to return '0'.  So, I tried this:
>
> select case(sum(numbers)) when null then 0 else sum(numbers) end from
> list_results;

I believe the case would be:

case when sum(numbers) is null then 0 else sum(numbers) end

It's more like the ternary operator than a switch statement.