SQL计数多个列和行的特定值

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?

我想计算表中出现“1”的次数,因此在这种情况下,查询应该以12结果。我尝试“硬编码”它,就像下面的查询一样。但这只会导致行包含1,所以在这种情况下4.如何计算'1'出现的次数,从而导致计数为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'
)

5 个解决方案

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

#2

This is very weird assignment but:

这是非常奇怪的任务,但是:

http://sqlfiddle.com/#!9/2e7aa/3

SELECT SUM((q1='1')+(q2='1')+(q3='1')+(q4='1')+(q5='1')+(q6='1')+(q7='1')) 
FROM table
WHERE Company = 'abc'
AND '1' IN (q1,q2,q3,q4,q5,q6,q7)

#3

Not so easy, each column needs to be hard-coded. I'd try something using a CASE or DECODE.

不那么容易,每列需要硬编码。我会尝试使用CASE或DECODE。

SELECT 
SUM(
CASE WHEN q1 = 1 THEN 1 ELSE 0 END +
CASE WHEN q2 = 1 THEN 1 ELSE 0 END +
CASE WHEN q3 = 1 THEN 1 ELSE 0 END +
CASE WHEN q4 = 1 THEN 1 ELSE 0 END +
CASE WHEN q5 = 1 THEN 1 ELSE 0 END +
CASE WHEN q6 = 1 THEN 1 ELSE 0 END +
CASE WHEN q7 = 1 THEN 1 ELSE 0 END)
FROM table
WHERE Company = 'abc'

Using a SUM instead of a COUNT will allow the CASE statement to be SUMed.

使用SUM而不是COUNT将允许CASE语句被SUMed。

#4

Use conditional COUNT

使用条件COUNT

SELECT COUNT(case when q1 = '1' then 1 end)  + 
       COUNT(case when q2 = '1' then 1 end)  + 
       COUNT(case when q3 = '1' then 1 end)  + 
       COUNT(case when q4 = '1' then 1 end)  + 
       COUNT(case when q5 = '1' then 1 end)  + 
       COUNT(case when q6 = '1' then 1 end)  + 
       COUNT(case when q7 = '1' then 1 end) as ones_total
FROM table
WHERE Company = 'abc'

#5

For reasons of efficiency I don't recommend actually using this approach; but for learning purposes here's another way you could have broken down the problem along the lines of the way you were thinking about it.

出于效率原因,我不建议实际使用这种方法;但是出于学习目的,这是另一种方法,你可以按照你想到的方式解决问题。

select sum(c) as total_ones
from
    (
    select count(*) c from table where q1 = 1 union all
    select count(*)   from table where q2 = 1 union all
    select count(*)   from table where q3 = 1 union all
    select count(*)   from table where q4 = 1 union all
    select count(*)   from table where q5 = 1 union all
    select count(*)   from table where q6 = 1 union all
    select count(*)   from table where q7 = 1
    ) t

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

#2