CODEWITHSUNDEEP
phpmysqlsql
user2914019
user2914019

Reputation: 11

MySQL Count Multiple Columns

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..

Upvotes: 1

Views: 680

Answers (3)

user2656474
user2656474

Reputation: 81

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.

Upvotes: 0

Pramod
Pramod

Reputation: 1041

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

Upvotes: 0

John Woo
John Woo

Reputation: 263693

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

Upvotes: 1

Related Questions