From: Aaron Bono <postgresql@aranya.com>
To: Vinnie Ma <nyubambam@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL (Venn diagram type of logic)
Date: Tue, 4 Jul 2006 11:55:06 -0500
Message-ID: <bf05e51c0607040955o117dce1fvdfdc70a8e62b175c@mail.gmail.com> (raw)
In-Reply-To: <20060629214214.95052.qmail@web52007.mail.yahoo.com>
References: <20060629214214.95052.qmail@web52007.mail.yahoo.com>
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
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: postgresql@aranya.com, nyubambam@yahoo.com
Subject: Re: SQL (Venn diagram type of logic)
In-Reply-To: <bf05e51c0607040955o117dce1fvdfdc70a8e62b175c@mail.gmail.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