SQL How to count the number of specific values ​​in a row

Question

Let's say I have a table with 50 fields. 20 of these fields may contain “YES”, “NO” or “N / A”. How to request the quantity "YES" for a given string?

Answer 1

You write a long statement that adds 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 

It would be much easier if you normalized the data, so each value was on a separate line. You would do this with a separate table called a join or association table.

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

Note: this is generic ANSI SQL because you are not specifying a database. There may be several shortcuts for writing code in different databases.