13 Replies Latest reply: Apr 26, 2011 3:46 PM by Anil Konduri RSS

    Count help

    Anil Konduri

      Hi All,

      I have data something like this

      Acct_Prod:
      LOAD * INLINE [
      Acct, Product
      Acct_A, Prod_A
      Acct_A, Prod_B
      Acct_B, Prod_A
      Acct_B, Prod_B
      Acct_C, Prod_B
      Acct_C, Prod_C
      ];

      want to calculate Avg products per purchasing Acct

      the total number of Products purchased by purchasing Accts / total number of unique purchasing Accts

      E.g.


      Acct Acct_A purchased Prod_A, Prod_B.
      Acct Acct_B purchased Prod_A and Prod_B, and
      Acct Acct_C purchased Prod_B and Prod_C.

      For Prod_A:

      Total number of products purchased = 4 (Prod_A, Prod_B (for Acct_A), Prod_A, Prod_B (for Acct_B))
      Total number of purchasing Accts 2. (Acct_A, Acct_B)

      Avg products per purchasing Acct = 4/2.

      Thanks in Advance