Home  >  Q&A  >  body text

Proper use of the COUNT function in MySQL

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?

P粉203648742P粉203648742217 days ago359

reply all(1)I'll reply

  • P粉759451255

    P粉7594512552024-04-05 13:08:43

    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;

    reply
    0
  • Cancelreply