How do I count two different values for specific ID?

Question

So I have a table that I populate with data from a quiz I have made. Why? So that I can spot questions that have a particular high/low success rate to determine if a questions need to be reworded. I think it will come in handy =) Any way, I'm having some troubles with "grouping" the answers to one row, and counting the occurrence of "correct" and "wrong".

The table looks like this:

+----+---------+----------+-------------+---------+------------+------------+
| id | user_id | stack_id | question_id | answer  | created_at | updated_at |
+----+---------+----------+-------------+---------+------------+------------+
|  1 |      34 |        1 |          45 | wrong   | NOW()      | NOW()      |
|  2 |      55 |        1 |          45 | correct | NOW()      | NOW()      |
|  3 |       2 |        3 |          13 | wrong   | NOW()      | NOW()      |
|  4 |      99 |        1 |          46 | correct | NOW()      | NOW()      |
|  5 |      12 |        3 |          13 | wrong   | NOW()      | NOW()      |
|  6 |      12 |        2 |          10 | correct | NOW()      | NOW()      |
+----+---------+----------+-------------+---------+------------+------------+

The column names correct, the data is mockup. But what I want is to group all question_ids, and count how many wrong and how many correct exists for that question_id.

(Im no sql guy, so please, if there is another way to display this data feel free!)

But the expected result should be something like:

+----------+-------------+-------+---------+
| stack_id | question_id | wrong | correct |
+----------+-------------+-------+---------+
|        1 |          45 |     1 |       1 |
|        3 |          13 |     1 |       0 |
|        1 |          46 |     0 |       1 |
|        2 |          10 |     0 |       1 |
+----------+-------------+-------+---------+

This is what I have so far, and it isn't much: SELECT answer, COUNT(*) FROM answers GROUP BY answer, and it seems to display the wrong information as well.

@Strawberry It's like 5 of 50 mysql questions (10%) I've read today. It even beats the most-recent-row-per-group questions :-) — Paul Spiegel, May 26 '18 at 21:41

score 1 · Answer 1 · answered May 26 '18 at 19:18

Try this out:

    select stack_id,question_id,sum(case when answer='wrong' then 1 else 0 end) as wrong,
           sum(case when answer='correct' then 1 else 0 end) as correct
    from your_table
    group by stack_id,question_id;

Let me know if it works.

score 1 · Answer 2 · answered May 26 '18 at 19:19

You can use the below SQL, to get the desired result:

select stack_id,question_id,
sum(case when answer='wrong' then 1 else 0 end) as wrong,
sum(case when answer='correct' then 1 else 0 end) as correct
from your_table_name
group by stack_id,question_id;

score 1 · Answer 3 · answered May 26 '18 at 20:39

1

If this is MySQL, I would use this logic:

select stack_id, question_id,
       sum(answer = 'correct') as correct, sum(answer = 'wrong') as wrong
from t
group by stack_id, question_id;

answered May 26 '18 at 20:39

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered May 26 '18 at 19:22

0

SELECT
    Answer
    , QuestionID
    , Count(Answer)
GROUP BY
   Answer, 
   QuesitonID

answered May 26 '18 at 19:22

Doug Coats

6,255
9
27
49

How do I count two different values for specific ID?

4 Answers4