count values in multiple columns mysql

Question

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?

can you post your sql statements to let us see what to combine? — Feanor
– Feanor, Commented Jul 4, 2012 at 11:39
if you would have used int values (and not strings that can create problems), let's say 1 for yes, 0 for no you could have used a sum on each column — mishu
– mishu, Commented Jul 4, 2012 at 11:39
In theory I think it might be possible but i suspect it would require a lot of self-joining and Unions to do. — GordonM
– GordonM, Commented Jul 4, 2012 at 11:40
It's best (or at least better) practice to store boolean values as TINYINT (0 for no, 1 for yes). — Sherlock
– Sherlock, Commented Jul 4, 2012 at 11:49

Michael Berkowski · Accepted Answer · 2012-07-04 11:45:11Z

4

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

edited Jul 4, 2012 at 11:45

answered Jul 4, 2012 at 11:39

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

Collectives™ on Stack Overflow

count values in multiple columns mysql

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