Bala Murugan Bala Murugan - 1 month ago 5
MySQL Question

How to fetch column name which contains 0 value

i have a table t1. which has the following value.

+----+-----+-----+-----+-----+-----+
| t1 | s1 | s2 | s3 | s4 | s5 |
+----+-----+-----+-----+-----+-----+
| 1 | 0 | 1 | 0 | 0 | 1 |
|----|-----|-----|-----|-----|-----|
| 2 | 1 | 0 | 0 | 1 | 0 |
+----+-----+-----+-----+-----+-----+


how to fetch column name. which has the value 0.

Answer

I think, you are trying to get the column/field name which has all the rows 0, or which has all its values as 0. For example: column s3 in your case.

One possible way to get the column is following: You first find the sum of each column. Now if a column has all its values as 0, sum for this column would be zero. Having done that, you use an " if " condition to return only those column whose sum is 0. You could also use "concat_ws" to comma separated those columns, in case you have more than one. So, here is the query. Also, let me draw the table again. Its a bit confusing the way you have drawn.

You table name is t1. It has five columns s1,s2,s3,s4,s5 and a total of two rows. You look to find s3, which has all its values as 0.

                        # Table t1 #

s1 s2 s3 s4 s5
0 1 0 0 1
1 0 0 1 0

select concat_ws( ',' , if(sum(s1)=0 , 's1' , null) , if(sum(s2)=0 , 's2' , null) , if(sum(s3)=0, 's3' , null) , if(sum(s4)=0, 's4', null), if(sum(s5)=0, 's5', null) ) as empty_column from t1;