A counting question

I'm hoping that someone has the answer. I guess I may be missing the obvious but I'm struggling to do this.

Simplified, I have two tables:- One table contains a list of Customers and the other table a list of Products that they have purchased. They are linked by a customer reference no, a one-to-many relationship.

What I want to be able to do is count have how many Customers have purchased both Product A and Product B.

Can anyone tell me the SQL statement that would return the answer as a single number.

Thanks in expectation

Ventus:

I am going to assume that the customer records are unique by customer. I am going to use exists clause because it is likely that a customer
has made multiple purchases of a particular product:

select count(*)
from CUSTOMER a
where exists (select 0 from product b where a.cust_id = b.cust_id and b.product = 'Product A')
and exists (select 0 from product c where a.cust_id = c.cust_id and c.product = 'Product B')

Dave

|||

Dave thanks. Still struggling and think the suggested solution may be too complex for what I want to do.

Imagine two tables, Customer and Product,

Customer has the field CUST_ID and Product the fields CUST_ID and PRODUCT_BOUGHT (Customer and Product are linked by a one-to-many relationship on Customer.CUST_ID = Product.CUST_ID)

The contents of Customer.CUST_ID are: 1; 2; 3; 4;

The contents of Product.CUST_ID, Product.PRODUCT_BOUGHT are: 1, A; 1, B, 2, A; 3, A; 3, B; 4, A;

The SQL statement I need counts the number of Customer.CUST_ID who bought both product A and B.

So in this case the answer returned would be 2.

Sorry if I'm not explaining myself all that well, or formatting the question in the correct way.

Thanks again for taking the time to help

|||

Here is generic way to do this query (assumes that CUST_ID, PRODUCT_BOUGHT is unique):

select p.CUST_ID

from Product as p
where p.PRODUCT_BOUGHT IN ( <list of products> )

group by p.CUST_ID

having count(p.PRODUCT_BOUGHT) = <number of products>

For your example, to get customers who bought only A & B together you would do:

select p.CUST_ID

from Product as p
where p.PRODUCT_BOUGHT IN ( 'A', 'B' )

group by p.CUST_ID

having count(p.PRODUCT_BOUGHT) = 2

There are other questions you can formulate and answer using the same query. To make it generic, you can pass the products as a list and do:

select p.CUST_ID

from Product as p
where p.PRODUCT_BOUGHT IN ( select s.value from SplitStr('A, B') as s )

group by p.CUST_ID

having count(p.PRODUCT_BOUGHT) = (select count(*) from SplitStr('A, B') as s)

The COUNT(DISTINCT) can be made to COUNT(*) >= @.cnt if you want to get customers who bought A & B only or A & B with other products. Ex:

-- Obtain customers who bought A & B only or A & B with some other product(s)

select p.CUST_ID

from Product as p
where p.PRODUCT_BOUGHT IN ( select s.value from SplitStr('A, B') as s )

group by p.CUST_ID

having count(p.PRODUCT_BOUGHT) >= (select count(*) from SplitStr('A, B') as s)

|||

Umachandar

Thanks, that was just the guidance I needed.

I've got my solution sorted now

Most helpful