MySQL Count Multiple Columns

Question

I have a table called "questionnaire" which has the following structure:

user_id   |      Q01      |       Q02        |      Q03         |
00001     |      Yes      |      Yes         |      Yes         |
00002     |      Yes      |      No          |      Yes         |
00003     |      Yes      |      No          |      No          |

I am figuring out on how to count the number of "Yes" for each User (user_id). For example:

00001 - 3
00002 - 2
00003 - 1

I just want to display the results without the UserID..

Before this, I have stored the Yes and No as "1" and "0", therefor I could use the following:

SELECT CONCAT(Q01+Q02+Q03) FROM `#__table` WHERE `id` = '[user_id]'

I cant seem to find the right query since its no longer an integer..

you mess up with table structure.

Suresh Kamrushi
– Suresh Kamrushi

2013-10-24 03:58:20 +00:00
Commented Oct 24, 2013 at 3:58 — Suresh Kamrushi
– Suresh Kamrushi, Commented Oct 24, 2013 at 3:58
A database table is NOT a spreadsheet. Normalize your data.

Strawberry
– Strawberry

2013-10-24 08:25:37 +00:00
Commented Oct 24, 2013 at 8:25 — Strawberry
– Strawberry, Commented Oct 24, 2013 at 8:25

peterm · Accepted Answer · 2013-10-24 04:05:37Z

1

SELECT (Q01 = 'Yes') + (Q02 = 'Yes') + (Q03 = 'Yes')
FROM #__table 
WHERE id = '[user_id]'

edited Oct 24, 2013 at 4:05

peterm

93k16 gold badges157 silver badges162 bronze badges

answered Oct 24, 2013 at 3:58

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

2 Comments

vhadalgi Over a year ago

could you tell me how to do the same in sql-server?

user2914019 Over a year ago

This is the one I am finding.. short and simple!

Pramod · Accepted Answer · 2013-10-24 04:15:25Z

0

SELECT if(Q01 = 'Yes',1,0) + if(Q02 = 'Yes',1,0) + if(Q03 = 'Yes',1,0)
FROM #__table 
WHERE id = '[user_id]'

answered Oct 24, 2013 at 4:15

Pramod

1,0313 gold badges13 silver badges26 bronze badges

Comments

user2656474 · Accepted Answer · 2013-10-24 04:18:19Z

0

SELECT sum(yes)
FROM (
        (SELECT count(*) yes
         FROM #__TABLE
         WHERE Q01='yes'
           OR Q02='yes'
           OR Q03='yes'
           AND user_id=00001)
      UNION ALL
        (SELECT count(*) yes
         FROM #__TABLE
         WHERE Q01='yes'
           OR Q02='yes'
           OR Q03='yes'
           AND user_id=00002)
      UNION ALL
        (SELECT count(*) yes
         FROM #__TABLE
         WHERE Q01='yes'
           OR Q02='yes'
           OR Q03='yes'
           AND user_id=00003)) AS tt;

i think this will work fine.

answered Oct 24, 2013 at 4:18

user2656474

815 bronze badges

Collectives™ on Stack Overflow

MySQL Count Multiple Columns

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related