CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110382

Count rows with equivalent value in two columns

I am looking to find the count of rows where the entered answer is the same as the correct answer. Here's an example:

WorkerID      Answer      Correct
1             A           A
1             B           C
2             A           D

I would then get the following result:

WorkerID    AnswerCount # Correct
1           2           1
2           1           0

So far I have (conceptually):

SELECT worker_id, count(*), count(Answer == Correct) FROM answer_table GROUP BY WorkerID

What would be the correct query here?

Upvotes: 3

Views: 66

Answers (3)

void
void

Reputation: 7890

use this:

select workerid,count(*) as numberOfAnswers,
sum(case
           when answer=correct then 1
           else 0 end) as correctAnswers
from tbl
group by workerid

DEMO

Upvotes: 1

Jean-François Savard
Jean-François Savard

Reputation: 21004

I think this is what you want : 

select count(*)
from yourTable
where answer = correct
group by workerId

Basically, what you need to do is

  • Select all where answer = correct.
  • group them by workerId.
  • count the num of rows (where answer = correct) for each group.

Edit : To answer to your edited question,

select count(*), count(b.workerId)
from yourTable
left join (select * 
           from yourTable 
           where answer = correct) b using(workerId)
group by workerId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You don't want count(), you want sum():

SELECT worker_id, count(*) as AnswerCount, sum(Answer = Correct) as NumCorrect
FROM answer_table
GROUP BY WorkerID;

count() counts the number of non-NULL values that the expression takes on. You want to count the number of matches, which is the number of trues.

Upvotes: 3

Related Questions