Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA00655 for ; Sun, 15 Aug 1999 11:51:05 -0400 (EDT) (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 LAA10081; Sun, 15 Aug 1999 11:48:41 -0400 (EDT) To: Herouth Maoz cc: "Len Morgan" , pgsql-sql@postgreSQL.org Subject: Re: [SQL] Agregate Problem? In-reply-to: Your message of Sun, 15 Aug 1999 17:27:57 +0300 Date: Sun, 15 Aug 1999 11:48:41 -0400 Message-ID: <10079.934732121@sss.pgh.pa.us> From: Tom Lane > At 17:01 +0300 on 15/08/1999, Len Morgan wrote: >> SELECT itemno,sum(qty) FROM sales WHERE sale_date = 'now()::date' This is invalid as it stands --- you need "GROUP BY itemno" because of the aggregate function. (IIRC, Postgres versions before 6.5 were a bit lax about checking for that error.) >> I would expect to get back zero rows if there were no sales today but I get >> back 1. Of course I also get back 1 row if I only sold 1 item. Is this a >> bug or a "feature?" Herouth Maoz writes: > This is a correct behaviour when there is no GROUP BY. PostgreSQL does this > in GROUP BY, too, unfortunately. I believe we finally agreed that producing an empty row when there is a GROUP BY is a bug (there was some debate about it). I intend to change that behavior for 6.6, but haven't actually done it yet... regards, tom lane