CODEWITHSUNDEEP
mysqlsql
user2517372
user2517372

Reputation:

Mysql combinations of multiple coloumns

I have my table in this format

item  A  B  C  D
i1    4  0  2  0
i2    0  2  1  0
i3    2  0  0  2
i4    3  0  1  1

And, I'm looking for output where two columns are taken in combinations and if both elements value is >0 output value is taken as 1. And total count is calculated from all records

w1 w2   out
A  B    0
A  C    2
A  D    2
B  C    1
B  D    0
C  D    1

i,e for columns (A,C)>0 only i1 and i4 satisfy.So out=2

So far, I have solved this by querying for each item and then summing the value in php. Can this be possible entirely by sql query?

Upvotes: 2

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do it in SQL, but I think you still have to consider all the different combinations. Here is one approach using union all and conditional aggregation:

select col1name, col2name,
       sum(col1 > 0 and col2 > 0)
from (select 'A' as col1name, 'B' as col2name, A as col1, B as col2
      from t
      union all
      select 'A', 'C', A, C
      from t
      union all
      select 'A', 'D', A, D
      from t
      union all
      select 'B', 'C', B, C
      from t
      union all
      select 'B', 'D', B, D
      from t
      union all
      select 'C', 'D', C, D
      from t
     ) t

EDIT:

There is another way, if you unpivot the data. This starts with this query:

select item, n.colname,
       (case when n.colname = 'A' then A
             when n.colname = 'B' then B
             when n.colname = 'C' then C
             else D
        end) as colval
from t cross join
     (select ';A' as colname union all select 'B' union all select 'C' union all select 'D'
     );

We can now do a self join on the query to get all combinations and aggregate to get the results:

select col1.colname, col2.colname,
       sum(col1.colval > 0 and col2.colval > 0)
from (select item, n.colname,
             (case when n.colname = 'A' then A
                   when n.colname = 'B' then B
                   when n.colname = 'C' then C
                   else D
              end) as colval
      from t cross join
           (select ';A' as colname union all select 'B' union all select 'C' union all select 'D'
           )
     ) col1 join
     (select item, n.colname,
             (case when n.colname = 'A' then A
                   when n.colname = 'B' then B
                   when n.colname = 'C' then C
                   else D
              end) as colval
      from t cross join
           (select ';A' as colname union all select 'B' union all select 'C' union all select 'D'
           )
     ) col2
     on col1.item = col2.item and
        col1.colname < col2.colname
group by col1.colname, col2.colname;

This version is simpler if you have more than four columns. The number of combinations in the first methods will quickly become cumbersome.

Upvotes: 2

Related Questions