在两列中计算值相等的行

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?

这里正确的查询是什么?

3 个解决方案

#1

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

您不需要count()，您需要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.

count()计算表达式接受的非空值的数量。你要计算匹配数，也就是正确率。

#2

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.
集团通过workerId。
count the num of rows (where answer = correct) for each group.
计算每个组的行数(where answer = correct)。

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

#3

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

演示

#1