Merin - 1 year ago 39

SQL Question

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'

)

Answer Source

```
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'
```