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?

Think of this table as a Survey with 20 Questions that are answered "YES" "NO" or "N/A". Each row of the table is a users response to a survey. Can you provide a brief example to illustrate how to normalize this to better achieve the desired results? — Thomas Seavert
– Thomas Seavert, Commented Aug 13, 2014 at 11:26
Then you should create a table Survey (with a SurveyID, a UserID and whatever you need) and a table Question(with the text). Another table Result contains the YES, NO,N/A and a third table SurveyQuestion with SurveyID,QuestionID and ResultID(nullable to support unanswered questions) as foreign-keys to link all tables. — Tim Schmelter
– Tim Schmelter, Commented Aug 13, 2014 at 11:34

Gordon Linoff · Accepted Answer · 2014-08-13 11:12:04Z

7

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.

answered Aug 13, 2014 at 11:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL How to Count Number of Specific Values in a Row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related