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

8114 Workaround

The very simplified version of my problem is that these

Select DISTINCT Cast(KWID as NUMERIC)
FROM OV_MID

Select DISTINCT Convert(Numeric,KWID)
FROM OV_MID

should work, but don't because KWID is a varchar and somewhere in there is something that won't convert.

I get this error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I would love to find out which rows are causing the error, but more importantly I would like to have a Null value where the conversion doesn't work and the numeric values where it does work.

I have already deleted all obvious non-numeric characters, but I believe there are some line terminators being read as carriage returns in this table. :confused:

Any workaround or way to determine which rows have KWID that cannot be converted to numeric would be most appreciated.

Thanks!Just in case anyone actully reads this and has the same question. I solved it with:

SELECT
Case
When ISNUMERIC(KWID) = 1
THEN Cast(KWID as NUMERIC)
ELSE Cast(null as NUMERIC)
END
FROM OV_MID

maybe that will help someone else out who is going as crazy as I was. :o