SQL count specific value over multiple columns and rows

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

At heart, the reason your approach didn't and cannot work is that count() counts rows and so a set of 1s across multiple columns would only be counted a single time. — shawnt00
– shawnt00, Commented May 16, 2016 at 18:45

Nick · Accepted Answer · 2016-05-16 18:38:28Z

5

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'

answered May 16, 2016 at 18:38

Nick

10.2k8 gold badges65 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Merin Over a year ago

Ended up using this solution, exactly what I was looking for!

Alex · Accepted Answer · 2016-05-16 18:43:04Z

4

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)

answered May 16, 2016 at 18:43

Alex

17.3k1 gold badge31 silver badges52 bronze badges

1 Comment

Matt Raines Over a year ago

This is the shortest (and clearest) answer, relying on the fact that booleans are actually treated as integers 0 or 1 in MySQL. The addition of AND '1' IN (q1,q2,q3,q4,q5,q6,q7) probably makes it the most efficient too.

GavinCattell · Accepted Answer · 2016-05-16 18:38:24Z

3

Not so easy, each column needs to be hard-coded. I'd try something using a CASE or 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.

answered May 16, 2016 at 18:38

GavinCattell

4,01323 silver badges22 bronze badges

1 Comment

Juan Carlos Oropeza Over a year ago

Just personal preference, both query do the same result but using count would make it easier for others know what are you trying to do.

Juan Carlos Oropeza · Accepted Answer · 2016-05-16 18:38:33Z

3

Use conditional 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'

answered May 16, 2016 at 18:38

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Comments

shawnt00 · Accepted Answer · 2016-05-16 18:52:00Z

2

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

answered May 16, 2016 at 18:52

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Collectives™ on Stack Overflow

SQL count specific value over multiple columns and rows

5 Answers 5

1 Comment

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related