Merin Merin - 6 months ago 10
SQL Question

SQL count specific value over multiple columns and rows

I feel as if this should be quite easy, but can't seem to find a solution.
Suppose I have the following table:

|--------||---||---||---||---||---||---||---|
|Company ||q1 ||q2 ||q3 ||q4 ||q5 ||q6 ||q7 |
|--------||---||---||---||---||---||---||---|
|abc ||1 ||2 ||1 ||3 ||2 ||2 ||1 |
|abc ||2 ||2 ||1 ||2 ||3 ||1 ||1 |
|abc ||1 ||1 ||3 ||3 ||1 ||2 ||2 |
|abc ||1 ||2 ||1 ||3 ||0 ||1 ||3 |


I want to count the number of times '1' appears in the table, so the query should, in this case, result with 12. I tried 'hardcoding' it, like the following query. But that just results in the rows containing a 1, so in this case 4. How do I count the number of times '1' occurs, thus resulting in a count of 12?

SELECT COUNT(*)
FROM table
WHERE Company = 'abc'
AND (
q1 = '1'
OR q2 = '1'
OR q3 = '1'
OR q4 = '1'
OR q5 = '1'
OR q6 = '1'
OR q7 = '1'
)

mnv mnv
Answer
SELECT SUM(
    IF(q1 = 1, 1, 0) +
    IF(q2 = 1, 1, 0) +
    IF(q3 = 1, 1, 0) +
    IF(q4 = 1, 1, 0) +
    IF(q5 = 1, 1, 0) +
    IF(q6 = 1, 1, 0) +
    IF(q7 = 1, 1, 0)
)
FROM table
WHERE Company = 'abc'