Proper use of the COUNT function in MySQL

Question

I have 2 tables and this is my fiddle http://sqlfiddle.com/#!9/da5e4e/3

So basically I have 2 tables called personal and interview_score where personal.id = Interview_score.personal_id.

Assume this is my personal table

id  name
1   John Doe
2   Nian
3   Rijali

This is my interview_score table

id  personal_id aspect_id
1   1   1
2   1   2
3   1   3
4   2   1
5   2   2

In this case I just want to use this query to calculate how many personal_id are in my interview_score

table

SELECT COUNT(i.id) as interviewed FROM personal p LEFT JOIN interview_score i ON i.personal_id = p.id GROUP BY i.personal_id;

But it returns like this

interviewed
0
3
2

At the same time, my expected result is this

interviewed
2

Because there are 2 personal_id based on this condition on this table.

Where did I go wrong?

Answer 1

You can use join instead of left join and also need to group by p.id

SELECT COUNT(i.id) as interviewed FROM personal p  JOIN interview_score i ON i.personal_id = p.id GROUP BY p.id;

From the above query you can get the count

Get the count of a specific person

SELECT COUNT(i.id) as interviewed FROM personal p  JOIN interview_score i ON i.personal_id = p.id and p.id=1 GROUP BY p.id;