Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.2/8.9.1) with ESMTP id NAA15457 for ; Sun, 14 Mar 1999 13:04:58 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id NAA29743 for ; Sun, 14 Mar 1999 13:04:27 -0500 (EST) To: pgsql-sql@hub.org Subject: Re: sum gives different answer In-reply-to: Your message of Sat, 13 Mar 1999 23:00:29 -0500 (EST) <199903140400.XAA11118@hub.org> Date: Sun, 14 Mar 1999 13:04:27 -0500 Message-ID: <29740.921434667@sss.pgh.pa.us> From: Tom Lane Chairudin Sentosa writes: > I have two SQL statements that I expect to give (0 rows) as output. > However the first one with 'sum' does not do that. > select pin, sum(duration) from tablename where date(start_time)= > (select date 'today') group by pin; > pin|sum > - ---+--- > | > (1 row) > select pin, duration from tablename where date(start_time)= > (select date 'today') group by pin; > pin|duration > - ---+-------- > (0 rows) As George Moga pointed out, SUM() applied to an empty collection of tuples still gives a result (one tuple containing NULL). Aggregates in general will give a result of some kind for an empty input collection --- for example, you'd surely want COUNT() to return 0 not nothing. However what you're talking about here is different: there are no groups in the result, therefore SUM() should never have been applied at all, not applied once to an empty set of tuples. I'm firmly of the opinion that the first example above is a bug. The hackers list has been around on this question a couple of times, and there are some folks who claim that the current behavior is OK, but I'm at a loss to follow their reasoning. regards, tom lane