5 Replies Latest reply: Jun 16, 2016 9:16 AM by Isaac Lin RSS

    Selecting two values (distinct sum)

      Hi there,

      I am trying to make a set analysis for a bit of SQL code which I have done which shows the correct number of distinct customers which have both products.


      select count(distinct ID) as Number

      from table1 x


      X.Year= '2016/05' and

      (X.Product_1 + X.Product_1 + X.Product_1 + X.Product_1 + X.Product_1 ) > 0  and

      ( X.Product_2+ X.Product_2+ X.Product_2) > 0 and

      ( X.Product_3+ X.Product_3+ X.Product_3) = 0 and

      ( X.Product_4+ X.Product_4) = 0

      (If these two products added together are greater than 0 then count as distinct 1 on ID).

      The set analysis I have been trying to work with is the following;

      COUNT ({< ID=P({<Product_1={">0"}>}) + P({<Product_1={">0"}>}) + P({<Product_1={">0"}>}) + P({<Product_1={">0"}>}) + P({<Product_1={">0"}>})

      +P ({<Product_2={">0"}>}) +P ({<Product_2={">0"}>}) +P ({<Product_2={">0"}>})

      >}  DISTINCT ID)


      This gives an incorrect number, should I be excluding the other tables also to get the correct figure, just like my SQL?