Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 1B4F0474E6D for ; Sun, 5 May 2002 02:19:41 -0400 (EDT) Received: from temp.joelburton.com (unknown [209.190.238.101]) by postgresql.org (Postfix) with ESMTP id E55E7474E42 for ; Sun, 5 May 2002 02:19:39 -0400 (EDT) Received: from joeltf0d13cyyp (unknown [209.190.238.100]) by temp.joelburton.com (Postfix) with SMTP id ABF632BD0D; Sun, 5 May 2002 02:23:32 -0400 (EDT) From: "Joel Burton" To: "Ian Barwick" , "Jakub Ouhrabka" Cc: Subject: Re: difficult query Date: Sun, 5 May 2002 02:19:34 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 In-Reply-To: <200205042019.34265.barwick@gmx.net> X-Archive-Number: 200205/35 X-Sequence-Number: 7671 > > I'm curious. I've been on a sql course long time ago where the tutor > > mentioned similiar problem as something special for sql smarties... And > > yesterday when I faced this problem (which can be solved perfectly with > > the outer join) I wasn't able to remember the solution... If there is > > any... Just out of curiosity... > > well, you could do it like this I suppose: > > SELECT 1 AS day, COUNT(data) FROM table1 WHERE day=1 > UNION > SELECT 2, COUNT(data) FROM table1 WHERE day=2 > UNION > SELECT 3, COUNT(data) FROM table1 WHERE day=3 > UNION > SELECT 4, COUNT(data) FROM table1 WHERE day=4 > > (and so on ad infinitum) > > although I expect E.F. Codd will be turning in his grave ;-) A little better, perhaps, but still a true hack: select alldays.d, sum(c) from (select 1 as d union all select 2 union all select 3 union all select 4 union all select 5 ...) as alldays left outer join d using (d) group by alldays.d; If you could write a function in plpgsql that returned a query result, you could use that as the from clause rather than the long union.