Multi-column grouping 1

[bubbagrub]

Hi,

I feel sure this should be possible, but can't quite figure it out. I have a database table that contains responses to a number of questions. Questions 77-83 are Yes/No questions, and I'd like to see, for each question, how many people answered yes and how many answered no. I can get the data I want like this:

mysql> select question, response_text, count(*) from response where question > 76 and question < 84 group by question, response_text;
+----------+---------------+----------+
| question | response_text | count(*) |
+----------+---------------+----------+
| 77 | FALSE | 5 |
| 77 | TRUE | 9 |
| 78 | FALSE | 2 |
| 78 | TRUE | 12 |
| 79 | FALSE | 10 |
| 79 | TRUE | 4 |
| 80 | FALSE | 4 |
| 80 | TRUE | 10 |
| 81 | FALSE | 12 |
| 81 | TRUE | 2 |
| 82 | FALSE | 12 |
| 82 | TRUE | 2 |
| 83 | FALSE | 13 |
| 83 | TRUE | 1 |
+----------+---------------+----------+
14 rows in set (0.01 sec)

However, the format I want it in would be more like:

Question | Count("Yes") | Count ("No")

I've tried Count (response_text="Yes") but that always gives 14 for all questions.

Can anyone help?

Thanks!

 

[feherke]

Hi

I usually use [tt]case[/tt].

[b]select[/b]
question, sum(case when response_text=[i]'TRUE'[/i] then 1 else 0 end) yes, sum(case when response_text=[i]'FALSE'[/i] then 1 else 0 end) no
[b]from[/b] response
[b]where[/b] question > 76 [b]and[/b] question < 84
[b]group[/b] by question

Note that I may have to adjust the true/yes and false/no values. I did not understood what the values really are.

Feherke.

http://rootshell.be/~feherke/

 

[bubbagrub]

That looks good! Thanks feherke; I'll give it a go.

 

[bubbagrub]

It worked! I just had to make one small change - putting in the "as" keyword, as follows:

mysql> select question, sum(case when response_text="TRUE" then 1 else 0 end) as yes, sum(case when response_text="FALSE" then 1 else 0 end) as no from response where question > 76 and question < 84 group by question;
+----------+------+------+
| question | yes | no |
+----------+------+------+
| 77 | 9 | 5 |
| 78 | 12 | 2 |
| 79 | 4 | 10 |
| 80 | 10 | 4 |
| 81 | 2 | 12 |
| 82 | 2 | 12 |
| 83 | 1 | 13 |
+----------+------+------+
7 rows in set (0.02 sec)

Thanks again!

 

[feherke]

Hi

It worked! I just had to make one small change - putting in the "as" keyword

Oops. Too many Oracle this days... Thanks for posting back the correction.

Feherke.

http://rootshell.be/~feherke/