Calculate counts

ramse8pc · #1 (**permalink**) 12-14-10, 15:20

Hi,
I'm not much expert in writing sql statements and i have a requirement to calculate the number of customers subscribed for product.

My table has data like this.
Table name: Customer_Details

HTML Code:

Customer_Id   Product    
c1                  M1         
c2                  M3         
c3                  M3         
c1                  M2         
c1                  M3         
c2                  M4         
c3                  M2
c4                  M1
c4                  M3
c4                  M4
c4                  M5

I have another table PRODUCT_FLAG

HTML Code:

PRODUCT  FLAG
M1           Y
M2           Y
M3           N
M4           N
M5           Y

I need to get the customer counts for products in such a way that
if a customer has multiple Products with flag as Y, then he should be counted as 1 regardless of how many products he had.

eg: if c1 has M1,M2,M3--> counted as 1
if c2 had M3,M4--> counted as 0 etc;
Finally I need to get the output as follows:

HTML Code:

Product    distinct customer count
M1            2
M2            3
M3            1

Note:
If the customer has multiple products , that customer can be counted in any of the products.

Gurus, pls. help me write a query.

dav1mo · #2 (**permalink**) 12-14-10, 16:29

How about something like(air code, so check it out):

Code:

select count(*) from
(select CD1.Customer_Id, min(CD1.Product)
  from Customer_Details CD1
inner join PRODUCT_FLAG PF1
   on CD1.Product = PF1.Product
  and PF1.Flag = 'Y'
where exists (select 1 from Customer_Details CD2
                          inner join PRODUCT_FLAG PF2
                             on CD2.Product = PF2.Product
                           and PF2.Flag = 'Y'
                   where CD2.Customer_Id = CD1.Customer_Id
                       and CD2.Product <> CD1.Product))

Dave

ramse8pc · #3 (**permalink**) 12-15-10, 09:42

Thanks for the reply.Your query gives the total customer count. but i need customer count for each product.

HTML Code:

Product    distinct customer count
M1            2
M2            3
M3            1

dav1mo · #4 (**permalink**) 12-15-10, 10:04

do you mean?

Code:

select CD1.Product, count(*)
   from Customer_Details CD1
inner join PRODUCT_FLAG PF1
   on CD1.Product = PF1.Product
  and PF1.Flag = 'Y'
group by CD1.Product

Internet Services

Web Development

Digital Marketing

Consumer Tech