sql: join and count

Question

I have three tabels in my MySQL database.

table department (int id, int departent_id);
table position (int id, int department_id)
table test (int id, int position_id)

Deparment have mutiple positions, test have multiple department. I need to count quantity of tests of each department.

Answer 1

try

SELECT count(*), d.department_id FROM department d 
INNER JOIN position p ON p.department_id = d.department_id
INNER JOIN test t ON t.position_id = p.id
GROUP BY d.department_id

The group statement groups the counts by department_id

Answer 2

SELECT COUNT(*), department.id 
FROM test 
INNER JOIN department 
ON department.id = test.department_id 
GROUP BY department.id;

Something like this?

are the positions relevant in the question you ask?