I have two tables (employees) (departments)

I want to generate the following output:

num_of_employees   department       employee_name
10                 marketing            john   
10                 marketing            mark

so on and so forth.

I tried using this`

select count(employee_ID) as "num of employees", department_name, employee_name
from employees e, departments d
where e.dept_id = d.dept_id
group by department_name, employee_name

Without adding the emp name I get the correct solution, but with the employee I get 1 in the num of employees

  • department is "marketing john" and employee_name is "10 marketing mark"? that looks very strange. – miracle173 May 17 '17 at 18:05
  • some guy edited it. it should be three columns (num_of_emloyees, department, employee_name) and the data should be like this (10, marketing, john) (10, marketing, <name of the 9 ppl in the marketing dept>) – Grayson May 18 '17 at 14:14
  • you can edit and repair it – miracle173 May 18 '17 at 19:41

You need to fetch the number of employees in a sub-query and then use this:

  q.total as "num of employees", 
  departments d join 
  employees e on d.dept_id=e.dept_id join    
      count(dept_id) as total, 
    group by department_id
    ) q on d.dept_id=q.dept_id
group by d.department_name, e.employee_name
  • 1
    your subquery contains a lot of useless thing. Especially the departments table d1 should be removed, – miracle173 May 19 '17 at 22:20
  • @miracle173 you are right. I changed my (sub)query. – Marco May 21 '17 at 8:20

Forget GROUP BY, this is much more effective with analytic functions.

Use outer join if needed (employees without departments, departments without employees).

  count(employee_ID) over (partition by department_name) as "num of employees", 
  department_name, employee_name
from employees e full outer join departments d on(e.dept_id = d.dept_id);

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. employee_name
    employees e, 
    departments d, 
    employees c
  e.dept_id = d.dept_id and
  group by 
    e. employee_name

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.