X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 818CAEFF83 for ; Fri, 6 Jun 2003 16:26:37 +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 39346-04 for ; Fri, 6 Jun 2003 16:26:36 +0000 (GMT) Received: from web13807.mail.yahoo.com (web13807.mail.yahoo.com [216.136.175.17]) by svr1.postgresql.org (Postfix) with SMTP id 7E13DEFF9B for ; Fri, 6 Jun 2003 16:26:35 +0000 (GMT) Message-ID: <20030606162635.97132.qmail@web13807.mail.yahoo.com> Received: from [216.173.173.66] by web13807.mail.yahoo.com via HTTP; Fri, 06 Jun 2003 09:26:35 PDT Date: Fri, 6 Jun 2003 09:26:35 -0700 (PDT) From: Chris Gamache Reply-To: cgg007@yahoo.com Subject: Elegant SQL solution: To: pgsql-sql@postgresql.org In-Reply-To: <20030606122001.GA30097@wolff.to> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS, IN_REP_TO X-Spam-Level: X-Archive-Number: 200306/39 X-Sequence-Number: 13797 There are so many (bad) ways to skin this cat... I'm looking for a more elegant solution. If I SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP BY month; It might only return month | rows -------+------ 1 | 234 3 | 998 4 | 403 5 | 252 10 | 643 12 | 933 I would like: month | rows -------+------ 1 | 234 2 | 0 3 | 998 4 | 403 5 | 252 6 | 0 7 | 0 8 | 0 9 | 0 10 | 643 11 | 0 12 | 933 I could create a one-column table with values 1 - 12 in it, and select from that table with a where clause matching "month". I could also create a view "SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST be a more elegant way to do this. Any thoughts? __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com