CODEWITHSUNDEEP
sqlsqliteandroid-sqlite
Shawn Lauzon
Shawn Lauzon

Reputation: 6282

Counting number of rows with foreign keys

I am using sqlite3 on Android. 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!

Upvotes: 1

Views: 1635

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42813

select dept.id, dept.name, e.num_employees , m.num_managers
from dept
left join (select count(*) as num_employees, dept_id from employee group by dept_id) e
on
dept.id = e.dept_id
left join (select count(*) as num_managers, dept_id from manager group by dept_id) m
on
dept.id = m.dept_id

Upvotes: 1

CL.
CL.

Reputation: 180192

Joining with both employee and manager results in all possible combinations of employees and managers. You could use COUNT(DISTINCT) to count only unique occurrences of these:

SELECT d.id,
       d.name,
       COUNT(DISTINCT e.id) AS num_employees,
       COUNT(DISTINCT m.id) AS num_managers
FROM dept AS d
JOIN employee AS e ON d.id = e.dept_id
JOIN manager  AS m ON d.id = m.dept_id
GROUP BY dept.id

However, generating all these combinations, only to filter them out later, is inefficient.

You could simply compute the counts separately, with correlated subqueries:

SELECT id,
       name,
       (SELECT COUNT(*)
        FROM employee
        WHERE dept_id = dept.id
       ) AS num_employees,
       (SELECT COUNT(*)
        FROM manager
        WHERE dept_id = dept.id
       ) AS num_managers
FROM dept

Upvotes: 4

Related Questions