Re: SQL (Venn diagram type of logic)

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(at)yahoo(dot)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