Home > Enterprise >  Count the no of columns with same value in SQL
Count the no of columns with same value in SQL

Time:09-11

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.

CodePudding user response:

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'
  •  Tags:  
  • sql
  • Related