SQL How to Count Number of Specific Values in a Row

Question

Lets say I have a table that has 50 Fields. 20 of those fields can contain the Value "YES" "NO" or "N/A". How do I query the number of "YES"s for a given row?

Answer 1

You write a long statement that adds up the values:

select ((case when value1 = 'Yes' then 1 else 0 end) +
        (case when value2 = 'Yes' then 1 else 0 end) +
        . . .
        (case when value50 = 'Yes' then 1 else 0 end)
       ) as NumYesses

This would be much easier if you normalized the data, so each value was in a separate row. You would do this by having a separate table, called a junction or association table.

Also, you can generate this code in a spreadsheet, such as Excel, by using formulas on the columns names (or by writing a query that uses metadata in your database).

Note: this is generic ANSI SQL, because you don't specify the database. There may be some shortcuts to writing the code in different databases.