Count the no of columns with same value in SQL

September 10, 2022

I have a database MySQL table as following

Id	Column1	Column2	Column3
First	value1	value1	value2
Second	value2	value1	value2

I want to retrieve the count of value1 for the row with id First example table as follows

Id	COUNT(value1)
First	2

I searched on google but found posts only regarding counting in one specific column.
Can anyone help me with the SQL query for retrieving the above type.

>Solution :

Use a case expression to determine for each column whether to count it or not e.g.

select
    case when Column1 = 'value1' then 1 else 0 end
    + case when Column2 = 'value1' then 1 else 0 end
    -- ...
from MyTable
where id = 'first'