MySQL Select multiple count in one query

Question

I have 2 tables - departments and employees. I want to select all columns from departments and also add a column with number of subdepartments and number of employees in each department.

Simplified tables look like this:

departments:

id      name            parent_id
---------------------------------
1       IT              NULL
2       HR              NULL
3       Bussiness       NULL
4       Web dev         1
5       Ecommerce       4
6       Advertisement   3
7       Control         3
8       Programmers     1

employees:

id      name            department_id
---------------------------------
1       Adam            1
2       Ben             8
3       Charles         7
4       David           4
5       Eugen           4
6       Frank           6
7       Gustav          6
8       Heremy          4
9       Igor            5
10      Jacob           3

Result I want is:

id      name            parent_id       department_count      employee_count
----------------------------------------------------------------------------
1       IT              NULL            2                     1
2       HR              NULL            0                     0
3       Bussiness       NULL            2                     1
4       Web dev         1               1                     3
5       Ecommerce       4               0                     1
6       Advertisement   3               0                     2
7       Control         3               0                     1
8       Programmers     1               0                     1

Here is my query, but it is not working properly. But if I remove 1 of the COUNT, the numbers are then correct. I think that the NULL values are the problem, but I don't know the solution.

SELECT d.*, COUNT(d2.parent_id) AS department_count, COUNT(e.id) AS employee_count
FROM department AS d
LEFT JOIN department AS d2 ON (d.id = d2.parent_id)
LEFT JOIN employees AS z ON (e.department_id = d.id)
GROUP BY d.id
ORDER BY d.id

Any improvement on my code is welcome.

I'm sorry, my mistake - it should have been 2

WellBloud
– WellBloud

2014-09-16 07:44:12 +00:00
Commented Sep 16, 2014 at 7:44 — WellBloud
– WellBloud, Commented Sep 16, 2014 at 7:44

M Khalid Junaid · Accepted Answer · 2014-09-16 07:46:13Z

7

Use d2.id in your count function with distinct,so the repeated children for a department will be counted only once

SELECT d.*,
COUNT(distinct d2.id) AS department_count,
COUNT(distinct e.id) AS employee_count
FROM department AS d
LEFT JOIN department AS d2 ON (d.id = d2.parent_id)
LEFT JOIN employees AS e ON (e.department_id = d.id)
GROUP BY d.id
ORDER BY d.id

Demo

answered Sep 16, 2014 at 7:46

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bulat · Accepted Answer · 2014-09-16 07:39:38Z

4

You can try correlated queries as they provide straightforward logic:

SELECT d.*, 
  (SELECT COUNT(*) FROM departments WHERE parent_id = d.id) as department_count,
  (SELECT COUNT(*) FROM employees WHERE department_id = d.id) as employees_count
FROM departments d

answered Sep 16, 2014 at 7:39

Bulat

6,9992 gold badges32 silver badges54 bronze badges

1 Comment

M Khalid Junaid Over a year ago

Correlated might be slow because they will be executed for each row of departments table

Collectives™ on Stack Overflow

MySQL Select multiple count in one query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related