MySQL CROSSTAB or PIVOT Query

Question

I have a table with scores in columns with 1,0,and NA for answer as results. The data in the table will continue to grow as more scores are submitted for all three answer.

   |id | username | answer1 | answer2 | answer3 |
    --------------------------------------------
   | 1 | John     |   1     |    0    |    1    |
   | 2 | Mike     |   NA    |    1    |    1    |
   | 3 | Jill     |   1     |    NA   |    0    |
   | 4 | Mary     |   1     |    1    |    1    |

I am trying to create a select query that will display the results listed below (Total1 will sum all 1s, Total 2 will sum all Os, and Totals will sum all NAs) from the table above. I am using a MySQL database.

   |questions | total_1  | total_0 |total_NA|
    ----------------------------------------
   | answer1  |    3     |    0    |   1    |
   | answer2  |    2     |    1    |   1    |
   | answer3  |    3     |    0    |   0    |

possible duplicate of http://stackoverflow.com/questions/8920626/is-it-possible-to-use-crosstab-pivot-query-in-mysql — varsha, Feb 05 '15 at 06:32
possible duplicate of [mysql pivot/crosstab query](http://stackoverflow.com/questions/12382771/mysql-pivot-crosstab-query) — Rigel1121, Feb 05 '15 at 06:33
I did see that question and answer. It was close to what I was asking however, I think the structure might be a little different than what I was asking. Thanks for help and providing the link for future searches. — Rob Sands, Feb 05 '15 at 06:49

score 1 · Accepted Answer · answered Feb 05 '15 at 06:29

Simple answer, but I've just had one cup of coffee yet...

select 'answer1' as questions,
       sum(case when answer1 = 1 then 1 end) as total_1,
       sum(case when answer1 = 0 then 1 end) as total_0,
       sum(case when answer1 = NA then 1 end) as total_NA
from tablename
UNION ALL
select 'answer2' as questions,
       sum(case when answer2 = 1 then 1 end) as total_1,
       sum(case when answer2 = 0 then 1 end) as total_0,
       sum(case when answer2 = NA then 1 end) as total_NA
from tablename
etc...

Amazing! It worked. Thanks for all your help. Enjoy the coffee. You made it look so simple. — Rob Sands, Feb 05 '15 at 06:40

MySQL CROSSTAB or PIVOT Query

1 Answers1