Count rows with equivalent value in two columns

Question

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?

You can combine conditions in your WHERE, such as WHERE (Answer = Correct) AND (your other criteria....). — Andy Lester
– Andy Lester, Commented Feb 19, 2015 at 22:16

Gordon Linoff · Accepted Answer · 2015-02-19 22:17:40Z

3

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.

answered Feb 19, 2015 at 22:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Jean-François Savard · Accepted Answer · 2015-02-19 22:18:29Z

1

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

edited Feb 19, 2015 at 22:18

answered Feb 19, 2015 at 22:13

Jean-François Savard

21k7 gold badges54 silver badges78 bronze badges

1 Comment

David542 Over a year ago

Please see updated question, as I cannot use a WHERE clause in the statement, it needs to be part of the select.

void · Accepted Answer · 2015-02-19 22:32:47Z

1

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

answered Feb 19, 2015 at 22:32

void

7,9503 gold badges29 silver badges47 bronze badges

Collectives™ on Stack Overflow

Count rows with equivalent value in two columns

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related