X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id B5D98CD7E1 for ; Thu, 10 Jul 2003 19:32:45 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 78365-06 for ; Thu, 10 Jul 2003 16:31:56 -0300 (ADT) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 08A7FCD83A for ; Thu, 10 Jul 2003 12:28:34 -0300 (ADT) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.9/8.12.9) with ESMTP id h6AFRWE7002012; Thu, 10 Jul 2003 09:27:32 -0600 (MDT) Date: Thu, 10 Jul 2003 09:20:43 -0600 (MDT) From: "scott.marlowe" To: Ali Adams Cc: Subject: Re: help yourself by helping others In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-MailScanner: Found to be clean X-MailScanner-SpamCheck: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/130 X-Sequence-Number: 14244 On Fri, 4 Apr 2003, Ali Adams wrote: > Dear All, > > I am new to Relational Databases and SQL and my background in ODBs is clouding my way to solving what seems to be a simple problem. I am sure many of you have met it many times. > > OK, I have a table as follows: > > > ID Machine Date Withdrawals > 1 1 01/01/2003 1101 > 2 2 01/01/2003 2101 > 3 3 01/01/2003 3101 > > 4 1 02/01/2003 1102 > 5 2 02/01/2003 2102 > > 6 1 03/01/2003 1103 > 7 3 03/01/2003 3103 > > 8 2 04/01/2003 2104 > 9 4 04/01/2003 4104 > > And i would like to create a monthly withdrawals report as follows: > > Machine Day1 Day2 Day3 Day4 .............................................. Day31 > 1 11 12 13 0 0 > 2 21 22 0 24 0 > 3 31 0 33 0 0 > 4 0 0 0 0 0 > > Can you please help? What you need is a table that has all the dates you want a report on already in it to join against. Just something like: day --- 1 2 3 4 5 6 7 ... 29 30 31 <- or fewer for other months. Just make a temp table to hold the days. Then join against that using extract(). If you get stuck let us know.