SQL计算多个列和行上的特定值 [英] 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  |

我想计算表中出现"1"的次数，因此在这种情况下，查询结果应为12.我尝试对它进行硬编码"，就像下面的查询一样.但这只会导致行中包含1，因此在本例中为4.如何计算出现"1"的次数，从而得出12的计数?

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

推荐答案

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'

这篇关于SQL计算多个列和行上的特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！