Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Unanswered: Calculate counts

    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.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Dec 2010
    Posts
    2

    vollest "Geometrie

    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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •