Question 89299 in PostgreSQL Question posted in
The official documentation can be found here.

How to write a query that returns the id of the managers in the department of which no more than 5 people work?(postgreSQL)

How to write a query that returns the id of the managers in the department of which no more than 5 people work?

I have a data table employee

id integer – id employee

name char – name employee

department_id integer – id of the department in which the employee works

chief_flg boolean – flag that the employee is in a leadership position

birth_dt date – Date of Birth

salary integer – employee salary

I tried to write query like this (postgreSQL)
but I think you can do something through HAVING

SELECT COUNT(e.id)<5, e.id as id_column
FROM employee AS e
WHERE chief_flg = 'True'
GROUP BY e.id
Tags:

2

Answers


  1. 0

    Your query, no need for having:

    select e.* from 
FROM employee AS e
join (SELECT COUNT(*), e.department_id
        FROM employee AS e
        WHERE chief_flg = 'True'
        GROUP BY e.department_id) mng
    on (mng.department_id = e.department_id and mng.cc <= 5)
WHERE chief_flg = 'True'

    Rather join with inner select, that counts number of employees in departments.

    Login or Signup to reply.
  2. 0

    There are a few different ways of doing this. One method to consider is:

    SELECT c.id
FROM employee c INNER JOIN employee e
  ON c.department_id = e.department_id
WHERE c.chief_flag = True
GROUP BY c.id
HAVING COUNT(*) <= 5

    The basic idea is to join the chiefs (c.chief_flag = True) to all the employees in the same department (c.department_id = e.department_id). As you suggested, you can then use HAVING COUNT(e.id) <= 5 to limit to the departments with 5 or fewer employees.

    An alternative that might be easier to follow (although is possibly less performant):

    SELECT c.id
FROM employee c
WHERE chief_flag = True
 AND (SELECT count(*) FROM employee e WHERE e.department_id = c.department_id) <= 5

    This gets all rows with chief_flag = True and checks whether the number of employees in their department is 5 or fewer.

    Another option is something like:

    SELECT id
(
 SELECT id, chief_flag, count(*) OVER (PARTITION BY department_id) dep_count
 FROM employee
) mt
WHERE chief_flag = True AND dep_count <= 5

    This is similar to the other answer on this question (calculates the number of employees in each department) but uses a window function in a subquery to do so.

    Your existing attempt will not work (even if you clean up the syntax) since you are only looking at the table once and limiting to chief_flg = 'True'. Once you’ve limited the query to only consider chiefs, you have no way of counting the other employees. That’s why most solutions to this would either join, use a subquery, or use a window function.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search