Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6B93Na54868 for ; Wed, 11 Jul 2001 05:03:23 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 2.12 #1) id 15KFtv-000FoE-0U; Wed, 11 Jul 2001 10:03:20 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 3065012F8B; Wed, 11 Jul 2001 09:57:52 +0100 (BST) Message-ID: <00bd01c109e7$9056fd80$1001a8c0@archonet.com> From: "Richard Huxton" To: "Roberto Mello" , "Graham Vickrage" Cc: References: <20010710133623.A27302@cc.usu.edu> Subject: Re: Group by date_part Date: Wed, 11 Jul 2001 09:57:45 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200107/137 X-Sequence-Number: 3929 From: "Roberto Mello" > On Tue, Jul 10, 2001 at 08:04:55PM +0100, Graham Vickrage wrote: > > > > The statement I have only selects the count if there is at least 1 order for > > a particular day, which make sense. > > > > I however need a count of 0 for days that don't have any. Can anyone help? > > > > SQL: > > > > SELECT date_part('day', date), count(*) > > FROM client_order WHERE (date >= '01/05/01' AND date < '01/06/01') AND > > status = 'Processing' > > GROUP BY date_part('day', date); > > Didn't look very hard, but can't you just add a: > > CASE WHEN count(*) > 0 THEN count(*) ELSE 0 END AS count Don't think so - this one always catches me out, and I have to figure out how to solve it each time. The problem is that if there aren't any records with (eg) date = 2001-05-16 then there is simply nothing to return. I've only ever come up with two solutions: 1. Create a temporary table with all the dates required and a total field initialized to zero. Update the totals as desired then just read from the table. 2. Create a temporary table with the dates and join against it. Then you can use a CASE construct as above to get a zero. Which one I select depends on usage patterns. If the dates don't change much (e.g. weekending dates) then I'll use #2, otherwise #1. I'd be very interested in any clever way of doing this without a temporary table (or equivalent - if functions could return sets of values you could use that, but it's basically the same thing). - Richard Huxton