How do I count two different values for specific ID? [duplicate]

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
– Paul Spiegel, Commented May 26, 2018 at 21:41

G.Arima · Accepted Answer · 2018-05-26 19:18:42Z

1

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.

answered May 26, 2018 at 19:18

G.Arima

1,1711 gold badge6 silver badges13 bronze badges

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

Comments

user8406805 · Accepted Answer · 2018-05-26 19:19:28Z

1

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;

answered May 26, 2018 at 19:19

user8406805

Comments

Gordon Linoff · Accepted Answer · 2018-05-26 20:39:36Z

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, 2018 at 20:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Doug Coats · Accepted Answer · 2018-05-26 19:22:16Z

0

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

answered May 26, 2018 at 19:22

Doug Coats

7,2179 gold badges32 silver badges56 bronze badges

Collectives™ on Stack Overflow

How do I count two different values for specific ID? [duplicate]

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Linked

Related