CODEWITHSUNDEEP
phpmysql
Aaron Lumsden
Aaron Lumsden

Reputation: 424

count values in multiple columns mysql

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?

Upvotes: 4

Views: 1156

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

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 SUM().

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

Upvotes: 4

Related Questions