I have a database table that contains user submitted answers to 3 questions. all the answers to the questions are yes/no answers. Like so
username question1 question2 question3
user1 yes no yes
user2 yes yes yes
user3 yes no no
user4 no no no
What I want to do is collect the count of each 'yes' in each column. So I would have the amount of yes's for each question ie 'question1' = '3', question2 = '1' etc etc.
At the moment I have 3 separate statements for each question which works fine but I was just wondering if there is a way to combine these into one statement to make it more effective?
This can be done with a simple aggregate
SUM() (with no
GROUP BY) surrounding a
CASE statement. If the value is
yes, it returns a 1, and otherwise a 0. Those 1's are then added over the column via
SELECT SUM(CASE WHEN question1 = 'yes' THEN 1 ELSE 0 END) AS q1, SUM(CASE WHEN question2 = 'yes' THEN 1 ELSE 0 END) AS q2, SUM(CASE WHEN question3 = 'yes' THEN 1 ELSE 0 END) AS q3 FROM yourtable
MySQL will also permit a simple boolean comparison which returns 1 or 0, but this is not portable to other RDBMS.
/* Shorter version, only works for MySQL */ SELECT SUM(question1 = 'yes') AS q1, SUM(question2 = 'yes') AS q2, SUM(question3 = 'yes') AS q3 FROM yourtable