Selecting And Displaying The Count Of Rows With The Same Value In A Table

Basically this is a survey system and I want to count the Yes's and No's from my results table which has | q1 | q2 | q3 | q4 | q5 | q6 | q7 | q8 | q9 | q10 | | Yes | Yes

Solution 1:

Just use boolean expressions:

select ((q1 = 'Yes') + (q2 = 'Yes') + . . .  + (q10 = 'Yes')) as numYes

MySQL treats a conditional expression as a number in a numeric context, with 1 for true and 0 for false.

Solution 2:

In MySQL (only) true is 1 and false is 0, so summing a condition counts how many times it's true:

select
    sum((q1 = 'Yes') + (q2 = 'Yes') + ... etc) yeses,
    sum((q1 = 'No') + (q2 = 'No') + ... etc) nod
from mytable