The task is to display the name of the head of the department and the name of the department in which the employees completed the maximum number of projects.

I got such a request, but it does not display exactly what is needed.
Made it by example from here (second paragraph).

SELECT department_name, max_amount FROM ( SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X INNER JOIN ( SELECT MAX(projects_amount) AS max_amount FROM ( SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X ) Y ON projects_amount = max_amount

Conclusion, despite the fact that there are only three projects: Query result

What is the problem - I think you will understand if you look at the database schema. Each department should correspond to several projects, but departments and projects are connected through the table of employees - and therefore in my request each department has more records than necessary.
I grouped by department name, but it turns out that in each group there are employees of this department. How to link the tables differently or group them differently - I did not invent it, I am in a stupor. If you go on the other hand, grouped by project id - in each group there will be the number of employees who worked on the project is also different.


It is necessary to solve the problem according to this scheme. I know that it can be simplified, but the task is educational.
Database Creation Script: http://pastebin.com/hnHnENpX .

DB schema

Thank you in advance.

  • one
    And you have the raw data for verification incorrect. You have employees in every department who worked on all projects. count you don’t take it correctly, 10 is the number of employee projects. count(distinct rel_prj_id) will give the number of projects by department. Only with your data in all departments on 3 projects - Mike
  • @Mike, thanks for the reply, count (distinct - that was the problem! Yes, I thought that the data would have to be changed (they just gave it out.) Although I did count (distinct - even that shows wrong, without the administration department). - Max
  • one
    Does it really have to show all the departments? You have a task to get a department where the maximum number of projects. such a department may be one. Or a few, if they have the same number of projects - Mike
  • one
    With your data, if you add a distinct to both subqueries, it displays all 3 divisions. To get a boss, you need to paste the employees table across the id field of the superior in the top query (you will have to use aliases for the tables so that the fields are identical in name) and add the name from this second table to the grouping, then the number records in the group will not change - Mike
  • one
    Why 4? You don't have a single employee in the personnel department, so there will be no projects there - Mike

1 answer 1

 SELECT emp_first_name,emp_last_name,department_name, max_amount FROM ( SELECT dep_name AS department_name,e2.emp_first_name,e2.emp_last_name, COUNT(distinct rel_prj_id) AS projects_amount FROM employees e1 INNER JOIN departments ON e1.emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = e1.emp_id INNER JOIN employees e2 ON e2.emp_id=dep_manager_id GROUP BY dep_name,e2.emp_first_name,e2.emp_last_name ) X INNER JOIN ( SELECT MAX(projects_amount) AS max_amount FROM ( SELECT dep_name AS department_name, COUNT(distinct rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X ) Y ON projects_amount = max_amount

Although the top subquery looks a bit strange, it would be more logical to look if the departments were the first table and everything was glued to it. Although in this case, the order does not affect anything (since in the inner join, the tables on the left and right are equivalent).

  • one
    (to comment) Yes, exactly, I already blunted it. / Why count (1) in the second query? - Max
  • one
    @Max I watched this difference in my output with this subquery, just left :) removed from the answer - Mike
  • Great, thanks a lot! I can throw a penny on beer as a symbolic gratitude. - Max