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.

Code samples always help: what is the exact query you're trying that produces the second table output? — msanford, Jun 03 '16 at 18:29
@msanford I don't have a query that does that, all the queries I tried produced different table structures. — ybce, Jun 03 '16 at 18:50
@ybce take a look at my answer below and let me know if you have any further questions. If not, please mark the answer that helped you. Thanks! — thephatp, Jun 05 '16 at 18:41
@thephatp thank you so much for your answer, I've been without internet for the past few days so I'll read the answers tomorrow and get back to you. — ybce, Jun 05 '16 at 19:48

score 3 · Accepted Answer · edited Jun 03 '16 at 21:16

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

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

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

Even when you have the right schema, you still need the last step to show the desire result. — Juan Carlos Oropeza, Jun 03 '16 at 20:01
I'd even add a third table "PossibleAnswers" and set up a many-many relationship between Questions and PossibleAnswers, using QuestionResponse as the in-between table. OP needs to brush up on data normalization. — Spivonious, Jun 03 '16 at 20:46
@Spivonious, actually agree with you here about 3rd option, but didn't want to go too crazy. :) — thephatp, Jun 03 '16 at 20:58
Is better use Conditional `COUNT` instead of 4 subquery for each question. — Juan Carlos Oropeza, Jun 03 '16 at 21:17
@thephatp I know that the structure is not ideal but I couldn't change it for various reasons. It worked perfectly and thank you for your recommendation. — ybce, Jun 06 '16 at 14:32

score 0 · Answer 2 · edited May 23 '17 at 10:29

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

If you were going to propose an option that required a different table structure, why would you recommend something like this? I'm not trying to be critical, but I don't understand the value of this proposal, because (1) it's not clear what the relationship between the two tables/data is (other than the name), and the resulting query is seriously overcomplicated and hard to follow, considering you're recommending a different structure up front. Can you help clarify the benefit of this solution? — thephatp, Jun 03 '16 at 21:02
@thephatp Is true I create two tables, but I could easy add just two more subquery instead. So the advantage is you dont really need change the structure. — Juan Carlos Oropeza, Jun 03 '16 at 21:08
But creating two tables is definitively changing the structure. Sure it may "look" or "feel" more similar to the original structure than my solution does, but it keeps the answer equally as complicated. I'm assuming this is for more than just fun or learning, in which case maintainability, readability, and best practice should be strongly considered any final decision/solution. — thephatp, Jun 03 '16 at 21:16
@thephatp Again, you can replace the table for subquery. I use the tables because was testing the query. But I think you query is good for this case but mine is easy to scalable if more Question are add it. — Juan Carlos Oropeza, Jun 03 '16 at 21:24

Counting distinct values from multiple columns

2 Answers2