X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B6FB49FA322 for ; Thu, 29 Jun 2006 18:42:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 74348-05 for ; Thu, 29 Jun 2006 18:42:17 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web52007.mail.yahoo.com (web52007.mail.yahoo.com [206.190.48.90]) by postgresql.org (Postfix) with SMTP id F21CC9FA31F for ; Thu, 29 Jun 2006 18:42:16 -0300 (ADT) Received: (qmail 95054 invoked by uid 60001); 29 Jun 2006 21:42:15 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=1+W4ZCJHmVHdHKcU2ROFjuifKtRFpDlKmnuhnusBFaP7UW9maKV/KQd8PM705w5uQEVyRizi41RuaDE3Akz5jWKOP8USJuypxrjIPpCIgf/ctFCAazJE/+AncF/8TvqUPeJYdi8igV6b2i99AN/SVjh/EXUzRkybq4oCnVtgmFc= ; Message-ID: <20060629214214.95052.qmail@web52007.mail.yahoo.com> Received: from [64.157.92.130] by web52007.mail.yahoo.com via HTTP; Thu, 29 Jun 2006 14:42:14 PDT Date: Thu, 29 Jun 2006 14:42:14 -0700 (PDT) From: Vinnie Ma Subject: SQL (Venn diagram type of logic) To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/18 X-Sequence-Number: 25592 Hello everyone. I have a customer purchase table that keeps track of transaction details. I'm looking sum up total spent by each customer and classify each customer based on which products they bought. Data looks like... account_id, date, product_id, sale_price ----------------------------------------- cust1, 03/21/2005, prod1, 50 cust1, 03/22/2005, prod4, 35 cust1, 05/08/2005, prod2, 50 cust2, 04/21/2005, prod16, 20 cust3, 04/16/2005, prod1, 50 etc...... Setup: I'm picturing a Venn Diagram in my head but I need help with the decision logic to classify each customer. Example: Category A: Prod1, Prod2 Category B: Prod3, Prod4 Category C: All products Not in Class A or Class B -A customer who has bought Prod1, Prod2 would be in the A only category. -A customer who has bought Prod1, Prod3 would be in the Class A&B category -A customer who has bought Prod18 would be in the C category -A customer who has bought Prod4, Prod16 would be in the B&C category -A customer who has bought Prod1, Prod4, Prod15 would be in the A&B&C category -etc... Then for each comination of categories (7 in total?), i will need of number of accounts in that category and total spent by those accounts. Any help or direction would be greatly appreciated. Thank you in advance. -Vince __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com