CODEWITHSUNDEEP
sqloracle-database
DaviRoli
DaviRoli

Reputation: 59

How to count the employees in each department

Hoping you guys can give some insight oh how to modify my query.

Results needed: show ONLY employees who are in the department with more than 2 employees. -----------that means that my results should only give 7 lines (3 and 4 from departments sales and IT)

Any help is greatly appreciated.

I have this Query:

SELECT b.employee_id,b.salary, a.department_id, a.department_name,
  max(b.salary) over (partition by a.department_id) as max_sal
FROM        department a, employee b
WHERE       a.department_id(+) = b.department_id

These are my current results:

EMPLOYEE_ID     SALARY DEPARTMENT_ID DEPARTMENT_NAME         MAX_SAL
----------- ---------- ------------- -------------------- ----------
       7566       3000            10 ACCOUNTING                 3000 
       7999       2500            20 RESEARCH                   3000 
       7610       3000            20 RESEARCH                   3000 
       7921       2500            30 SALES                      3000 
       7952       2000            30 SALES                      3000 
       7900       3000            30 SALES                      3000 
       7934       1000            40 IT                         2900 
       7876       2000            40 IT                         2900 
       7788       2500            40 IT                         2900 
       7910       2900            40 IT                         2900 
       7603       4000            50 EXECUTIVE                  5000 
       7596       4500            50 EXECUTIVE                  5000 
       8000       2500                                          2500

13 rows selected

Upvotes: 1

Views: 4646

Answers (2)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

SELECT b.employee_id,b.salary, a.department_id, a.department_name,
  max(b.salary) over (partition by a.department_id) as max_sal
FROM        department a, employee b
WHERE       a.department_id(+) = b.department_id
AND         a.department_id IN (
    SELECT department_id from employee
    GROUP BY department_id
    HAVING COUNT(employee_id) > 2
)

Upvotes: 2

Andomar
Andomar

Reputation: 238096

You could use count(*) over () to determine the number of employees per department. Since you cannot use a window function in a where clause, this requires a subquery.

select  *
from    (
        select  e.employee_id
        ,       e.salary
        ,       e.department_id
        ,       d.department_name
        ,       max(salary) over (partition by e.department_id) as max_sal
        ,       count(*) over (partition by e.department_id) as dep_empl_count
        from    employee e
        join    department d
        on      e.department_id = d.id
        ) SubQueryAlias
where   dep_empl_count > 2

And please use a join rather than the dated col1(+) = col2 syntax.

Upvotes: 3

Related Questions