CODEWITHSUNDEEP
mysqlsql
Rob Sands
Rob Sands

Reputation: 65

MySQL CROSSTAB or PIVOT Query

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    |

Upvotes: 1

Views: 647

Answers (1)

jarlh
jarlh

Reputation: 44776

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...

Upvotes: 1

Related Questions