问题描述:

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 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
相关阅读:
Top