You can start by creating 3 views for your 3 categories:
CREATE OR REPLACE VIEW cat_a (
account_id,
sales_cat_a
) AS
SELECT
account_id,
sum(sale_price) as sales_cat_a
FROM
sales -- Assuming sales is the table with the data
WHERE
product_id in ('prod1', 'prod2')
GROUP BY
account_id
Then do the same with a cat_b and cat_c view.
This will give you subtotals for each category.
As for your Venn Diagram, these views may be helpful or may not. To determine what to do, please provide a little more information:
1. What do you want your final result to look like - what columns?
2. Will each row returned represent one account, one of the 7 sections of the diagram or a combination of these two?
3. When you say total amount do you total amount spent in that section of the diagram or the total amount spent by that person?
On 6/29/06, Vinnie Ma <nyubambam@yahoo.com> wrote: 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