Search code examples
sqlmysql

Count 'IN' value in MySQL from multiple non-normalized columns

I am trying to count the amount of times an int or string value is presented in a table row from several columns in a non-normalized table.

SELECT count(*) from persons
WHERE '1' IN (`column 1`, `column 2`, `column 3`,`column 4`) AND person_MyId='14';

What I trying to solve is if it's possible to count the amount of times the value of 1 was located in the four columns without normalizing that table? I'm trying to see what I am missing in my query to pull that off.

Solution

  • Use SUM,

    SELECT SUM( (col1 = 1) +  (col2 = 1) +  (col3 = 1) + (col4 = 1) ) AS totCount
FROM persons
WHERE person_MyId = 14;

    See example