Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA45037 for ; Thu, 24 Feb 2000 19:46:44 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id TAA23635; Thu, 24 Feb 2000 19:46:21 -0500 (EST) To: M.Mazurek@poznan.multinet.pl cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] date_part, too many results? In-reply-to: References: Comments: In-reply-to M.Mazurek@poznan.multinet.pl message dated "Thu, 24 Feb 2000 22:38:48 +0000" Date: Thu, 24 Feb 2000 19:46:20 -0500 Message-ID: <23632.951439580@sss.pgh.pa.us> From: Tom Lane M.Mazurek@poznan.multinet.pl writes: > clicked table is empty for bannerid=1, so why is that row? How to get rid > bannerdb=> select count(*),date_part('dow',data::datetime) from clicked > where bannerid=1 group by date_part('dow',data::datetime); > count|date_part > -----+--------- > 0| > (1 row) It's not date_part's fault, it's count's fault. There should be no rows out if you have GROUP BY and no groups, but 6.5 and prior Postgres releases emit a dummy row anyway. This is a longstanding bug in Postgres's handling of aggregate functions. It's fixed for 7.0, if you want to try running the beta version... if not, you could add HAVING count(*) > 0 as a temporary workaround. regards, tom lane