Counting distinct values from multiple columns

Question

I have this table:

╔════════════════╤═══════════════════╤═══════════╤═══════════╗
║ question1      │ question2         │ question3 │ question4 ║
╠════════════════╪═══════════════════╪═══════════╪═══════════╣
║ Agree          │ Disagree          │ Agree     │ Disagree  ║
╟────────────────┼───────────────────┼───────────┼───────────╢
║ Strongly Agree │ Strongly Disagree │ Agree     │ Disagree  ║
╚════════════════╧═══════════════════╧═══════════╧═══════════╝

I'm trying to write a query using COUNT() which shows the number of responses per question like this:

╔══════════╤════════════════╤═══════╤══════════╤═══════════════════╗
║ Question │ Strongly Agree │ Agree │ Disagree │ Strongly Disagree ║
╠══════════╪════════════════╪═══════╪══════════╪═══════════════════╣
║ Q1       │ 1              │ 1     │ 0        │ 0                 ║
╟──────────┼────────────────┼───────┼──────────┼───────────────────╢
║ Q2       │ 0              │ 0     │ 1        │ 1                 ║
╟──────────┼────────────────┼───────┼──────────┼───────────────────╢
║ Q3       │ 0              │ 2     │ 0        │ 0                 ║
╟──────────┼────────────────┼───────┼──────────┼───────────────────╢
║ Q4       │ 0              │ 0     │ 2        │ 0                 ║
╚══════════╧════════════════╧═══════╧══════════╧═══════════════════╝

I've tried several queries but it always gave me wrong results. Any help would be appreciated. Thanks.

Answer 1

Not sure why you chose to structure the table the way it is shown, but if you have the flexibility to change it, I'd suggest doing so. With the structure as presented now, not only do you have problems getting the right query with the results you desire, you also have a structure that doesn't lend itself to adding new questions without a DB schema update.

If you CANNOT modify the table structure SQL DEMO

SELECT 'Q1' as Question , 
        Count(CASE WHEN Question1 = 'Strongly Agree'    THEN 1 END) AS 'Strongly Agree',
        Count(CASE WHEN Question1 = 'Agree'             THEN 1 END) AS 'Agree',
        Count(CASE WHEN Question1 = 'Disagree'          THEN 1 END) AS 'Disagree',
        Count(CASE WHEN Question1 = 'Strongly Disagree' THEN 1 END) AS 'Strongly Disagree'
FROM QandR
UNION ALL
SELECT 'Q2' as Question , 
        Count(CASE WHEN Question2 = 'Strongly Agree'    THEN 1 END) AS 'Strongly Agree',
        Count(CASE WHEN Question2 = 'Agree'             THEN 1 END) AS 'Agree',
        Count(CASE WHEN Question2 = 'Disagree'          THEN 1 END) AS 'Disagree',
        Count(CASE WHEN Question2 = 'Strongly Disagree' THEN 1 END) AS 'Strongly Disagree'
FROM QandR
UNION ALL
SELECT 'Q3' as Question , 
        Count(CASE WHEN Question3 = 'Strongly Agree'    THEN 1 END) AS 'Strongly Agree',
        Count(CASE WHEN Question3 = 'Agree'             THEN 1 END) AS 'Agree',
        Count(CASE WHEN Question3 = 'Disagree'          THEN 1 END) AS 'Disagree',
        Count(CASE WHEN Question3 = 'Strongly Disagree' THEN 1 END) AS 'Strongly Disagree'
FROM QandR
UNION ALL
SELECT 'Q4' as Question , 
        Count(CASE WHEN Question4 = 'Strongly Agree'    THEN 1 END) AS 'Strongly Agree',
        Count(CASE WHEN Question4 = 'Agree'             THEN 1 END) AS 'Agree',
        Count(CASE WHEN Question4 = 'Disagree'          THEN 1 END) AS 'Disagree',
        Count(CASE WHEN Question4 = 'Strongly Disagree' THEN 1 END) AS 'Strongly Disagree'
FROM QandR

If you CAN change the structure

Here's what I'd recommend:

2 Tables: Question & QuestionResponse

1. Question has 2 columns
   1. id (int; autoincrement)
   2. Question (varchar)

2. QuestionRresponse has 3 columns
   1. id (int; autoincrement)
   2. QuestionId (int; FK to Question:id)
   3. Response (varchar)

Then you can get the data you're looking for with this query and output:

SELECT q.Question, qr.Response, Count(qr.Response) as Count
FROM  `Question` q
LEFT JOIN QuestionResponse qr ON q.id = qr.QuestionId
GROUP BY q.Question,qr.Response

Answer 2

First you need two tables Questions and Answers so you can perform LEFT JOIN and fill with NULL's

CREATE TABLE `Questions` (`id` int, `Question` varchar(17));  
INSERT INTO `Questions`  (`id`, `Question`)
VALUES
    (1, 'question1'),(2, 'question2'),(3, 'question3'),(4, 'question4');

CREATE TABLE `Answers`  (`id` int, `choice` varchar(17));
INSERT INTO `Answers`   (`id`, `choice`)
VALUES
    (1, 'Strongly Agree'),(2, 'Agree'),(3, 'Disagree'),(4, 'Strongly Disagree');

Then you need unpivot your table. MySQL - turn table into different table

select c.col,
       case c.col
            when 'question1' then question1
            when 'question2' then question2
            when 'question3' then question3
            when 'question4' then question4
       end as `data` 
from yourTable t     
cross join
       (
        select 'question1' as col
        union all select 'question2'
        union all select 'question3'
        union all select 'question4'
       ) c

Then you join both result together and perform a pivot MySQL pivot table

SQL Fiddle Demo

SELECT Question,
       COUNT(CASE WHEN data = 'Strongly Agree' THEN 1 END)    as `Strongly Agree`,
       COUNT(CASE WHEN data = 'Agree' THEN 1 END)             as `Agree`,
       COUNT(CASE WHEN data = 'Disagree' THEN 1 END)          as `Disagree`,
       COUNT(CASE WHEN data = 'Strongly Disagree' THEN 1 END) as `Strongly Disagree`
FROM (       
        SELECT Q.Question, A.choice, p.`data`
        FROM `Questions` Q
        CROSS JOIN `Answers` A
        LEFT JOIN (
                    select c.col,
                           case c.col
                             when 'question1' then question1
                             when 'question2' then question2
                             when 'question3' then question3
                             when 'question4' then question4
                           end as `data` 
                    from yourTable t     
                    cross join
                    (
                      select 'question1' as col
                      union all select 'question2'
                      union all select 'question3'
                      union all select 'question4'
                    ) c
                  ) P
              ON A.`choice` = p.`data` 
             AND Q.Question = P.`col`
     ) R           
GROUP BY  Question;

OUTPUT