From: Vinnie Ma <nyubambam@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: SQL (Venn diagram type of logic)
Date: Thu, 29 Jun 2006 14:42:14 -0700 (PDT)
Message-ID: <20060629214214.95052.qmail@web52007.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nyubambam@yahoo.com
Subject: Re: SQL (Venn diagram type of logic)
In-Reply-To: <20060629214214.95052.qmail@web52007.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox