I am trying to find the number of rows which are related to each row in the result. The following reflects the general organization of the tables, but the details are more complex:
dept:
+----+-----------------+
| id | name |
+----+-----------------+
employee:
+----+-----------------+-----------------+
| id | dept_id | name |
+----+-----------------+-----------------+
manager:
+----+-----------------+-----------------+
| id | dept_id | name |
+----+-----------------+-----------------+
I am trying to query all of the departments, and with each one display the count of the 1) employees and 2) managers. So the result might look something like:
id | name | num_employees | num_managers
1 | IBM | 10000 | 800
2 | FB | 8000 | 20
I would like to do this all in one query so that I can use a single CursorLoader for this. I've tried this, but it's not right:
SELECT d.id, d.name, COUNT(e.id), COUNT(m.id)
FROM dept d
INNER JOIN employee e ON d.id = e.dept_id
INNER JOIN manager m ON d.id = m.dept_id
GROUP BY d.id;
Thanks in advance for your help!
Aucun commentaire:
Enregistrer un commentaire