Mouser Mouser - 6 months ago 17
MySQL Question

Sum column values in flipped table with generated column names

I have the following MySQL-code:

select question,
sum(case when value = '1' then 1 else 0 end) '1',
sum(case when value = '2' then 1 else 0 end) '2',
sum(case when value = '3' then 1 else 0 end) '3',
sum(case when value = '4' then 1 else 0 end) '4',
sum(case when value = '5' then 1 else 0 end) '5',
sum(case when value = '6' then 1 else 0 end) '6',
sum(case when value = '7' then 1 else 0 end) '7',
sum(case when value = '8' then 1 else 0 end) '8',
sum(case when value = '9' then 1 else 0 end) '9',
sum(case when value = '10' then 1 else 0 end) '10',
count(value) AS total -- this line should be edited
from
(
select answer1 value, 'answer1' question
from questionaire
union all
select answer2 value, 'answer2' question
from questionaire
union all
select answer3 value, 'answer3' question
from questionaire
) src
group by question


This is a
select
statement that flips a table formatted like this:

id | e-mail | answer1 | answer2 | answer3
------------------------------------------------------
1 | test@example.com | 1 | 6 | 8
2 | test2@example.com| 1 | 1 | 7
3 | test2@example.com| 1 | 1 | 0


The flipped table looks like this

question | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | total
---------------------------------------------------------
answer 1 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3
answer 2 | 2 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 3
answer 3 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 3


There is a
total
column that counts all the values, however I want it to
sum
up all the values from columns before it. Because when somebody skips a questions it counts as a zero and the sum in total will be wrong (see the table, answer 3). The total is
3
, and should be
2
.

My question is how to sum up all the values from 1 to 10 within the total column?

Answer

I would write it instead as:

sum(value in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10') )

Or, perhaps more simply as:

sum(value > '0')