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
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粉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;