The "classical" way to achieve this using
group by is the following. You have to introduce a second employee table that represents the colleagues of an employee in the same departments.
Note that I assume that emp_id and dept_id are the primary keys and I do not assume that employee_name and department_name are unique. So I have to use the IDs in the group by clause even if I do not display them.
count(c.employee_id) as "num of employees",
e.dept_id = d.dept_id and
But maybe there are ways using newer SQL constructs with better performance. It would be interesting to test it and compare it to the other answers. It is impossible for me to predict which query has the better performance. Oracle has a smart optimizer so there is no reason to expect that he does something in a slow way if a faster one exists.