Count Variable column wise in MySQL

Question

I have a tableA

|Column1|Column2|Column3|
|NO     |NO     |YES    |
|YES    |YES    |NO     |
|NO     |NO     |NO     |

My requirement is to count the number of Yes or No per row and display the greatest (or equal to) of them. My sample output should be like:

|Column1|Column2|Column3|Answer
|NO     |NO     |YES    |No
|YES    |YES    |NO     |Yes
|NO     |NO     |NO     |No

I know i can use a where clause if my requirement was to count them column wise, but how do I get this to be done row wise?

If it is only a few columns, you can use a CASE WHEN clause — neelsg
– neelsg, Commented Dec 6, 2017 at 9:40

M Khalid Junaid · Accepted Answer · 2017-12-06 09:33:54Z

1

If there are only 3 fixed columns then you can get expected result set by using CASE statement

SELECT 
  *,
  CASE
    WHEN Column1 = Column2 
    THEN Column1 
    WHEN Column1 = Column3 
    THEN Column1 
    WHEN Column2 = Column3 
    THEN Column2 
    ELSE NULL 
  END answer 
FROM
  demo a

Demo

answered Dec 6, 2017 at 9:33

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harshil Doshi · Accepted Answer · 2017-12-06 09:33:11Z

0

Following query will work:

SELECT 
    c1,c2,c3,    
   (
     case when
       ROUND 
       (   
          (
            CHAR_LENGTH(concat_ws(" ",c1,c2,c3))
            - CHAR_LENGTH( REPLACE ( concat_ws(" ",c1,c2,c3), "NO", "") ) 
          ) / CHAR_LENGTH("NO")
       ) > ROUND 
       (   
          (
            CHAR_LENGTH(concat_ws(" ",c1,c2,c3))
            - CHAR_LENGTH( REPLACE ( concat_ws(" ",c1,c2,c3), "YES", "") ) 
          ) / CHAR_LENGTH("YES")
       )
        then 'NO'
        else 'YES'
        end
 )   
     AS Answer    
FROM t;

answered Dec 6, 2017 at 9:33

Harshil Doshi

3,6123 gold badges18 silver badges41 bronze badges

Collectives™ on Stack Overflow

Count Variable column wise in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related