X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C0A1F5E46F8 for ; Sun, 29 Aug 2004 02:21:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73810-05 for ; Sun, 29 Aug 2004 05:21:20 +0000 (GMT) Received: from ganymede.hub.org (blk-222-46-91.eastlink.ca [24.222.46.91]) by svr1.postgresql.org (Postfix) with ESMTP id 356B45E46D4 for ; Sun, 29 Aug 2004 02:21:21 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 49844395E2; Sun, 29 Aug 2004 02:21:21 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 4869E39039 for ; Sun, 29 Aug 2004 02:21:21 -0300 (ADT) X-Return-Path: X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.8) with LMTPA; Fri, 27 Aug 2004 17:21:38 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost.hub.org [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 0D2343870E for ; Fri, 27 Aug 2004 17:20:38 -0300 (ADT) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5) for scrappy@localhost (single-drop); Fri, 27 Aug 2004 17:20:38 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.8) with LMTPA; Fri, 27 Aug 2004 17:20:19 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 27A965E40BA for ; Fri, 27 Aug 2004 17:20:19 -0300 (ADT) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41896-09 for ; Fri, 27 Aug 2004 20:20:13 +0000 (GMT) X-Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id D87085E37D0 for ; Fri, 27 Aug 2004 17:20:12 -0300 (ADT) X-Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6195064; Fri, 27 Aug 2004 13:21:31 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Scott Gerhardt Subject: Re: Aggregate query for multiple records Date: Fri, 27 Aug 2004 13:19:41 -0700 User-Agent: KMail/1.4.3 References: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca> <200408271159.26445.josh@agliodbs.com> In-Reply-To: Cc: psql-sql@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200408271319.41343.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-DCC: : X-Spam-Pyzor: ReSent-Date: Sun, 29 Aug 2004 02:21:16 -0300 (ADT) Resent-From: "Marc G. Fournier" Resent-To: pgsql-sql@postgresql.org ReSent-Subject: Re: [SQL] Aggregate query for multiple records ReSent-Message-ID: <20040829022116.Y72121@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/319 X-Sequence-Number: 18816 Scott, > Unfortunately, your revised query works like a charm except for the > fact that prd_data."date" - prd2."date" + 1 give incorrect values when > the year wraps, see in the output below. Need to conditionally > subtract 88 from the date or use an incrementing count() function > instead of date math to get the correct values. Oh, that column is text, not a serial number. Well, it's your fault for using wierd custom data types; you figure it out. > I'm also wondering if using the date functions of Postgres would be > helpful since the date column does have the YYYY-year and MM-month > parts. Well, you'd want to convert the column to a timestamp, and then you could compute months. Or you could break it in seperate integer "year" and "month" columns and do the same thing. -- Josh Berkus Aglio Database Solutions San Francisco