X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2A764D1B4E3 for ; Mon, 22 Sep 2003 01:06:08 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 98614-02 for ; Sun, 21 Sep 2003 22:05:39 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id C79B2D1B553 for ; Sun, 21 Sep 2003 22:05:35 -0300 (ADT) Received: (qmail 10068 invoked by uid 500); 22 Sep 2003 01:12:30 -0000 Date: Sun, 21 Sep 2003 20:12:30 -0500 From: Bruno Wolff III To: Bob Hutzel Cc: pgsql-sql@postgresql.org Subject: Re: Cross joining table with itself Message-ID: <20030922011230.GA9968@wolff.to> Mail-Followup-To: Bob Hutzel , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/239 X-Sequence-Number: 15217 On Sun, Sep 21, 2003 at 14:38:23 +0000, Bob Hutzel wrote: > I've been stumped trying to solve this problem via SQL. I have a table ID, > member1, member2, member3. In any row, any member field may have a value > or be null. I want to tally up how many times each member appears in the > table as a whole. For example, if two entries were ('a', '', 'c') and > ('b', 'c', ''), I'd like my final result to be: > > a, 1 > b, 1 > c, 2 > > Is this feasible? Thank you for any help. You can 'union all' selects of each of the three columns where the appropiate member value is not null and use group by and count on the result.