明智的员工部门，员工人数超过5名 [英] employee department wise and count of employees more than 5

问题描述

我想显示department_id以及数量，并且数量应大于5，并且我想让一月份没有被雇用的员工.

i want to display department_id's along with count,and count should be more than 5, and i want to have employees who are not hired in January.

我尝试了以下查询

SELECT * FROM EMPLOYEES 
WHERE DEPARTMENT_ID IN
 (
   SELECT DEPARTMENT_ID
    FROM EMPLOYEES
   GROUP BY DEPARTMENT_ID
   HAVING COUNT(*)>5 
 )
AND HIRE_DATE NOT LIKE '%JAN%';

但是在这里我没有数.我也想数.

but here I didnt get count.I want count Also.

推荐答案

SELECT department_ID, count(employee_id) as '# of Employees' FROM EMPLOYEES 
WHERE DEPARTMENT_ID IN
 (
   SELECT DEPARTMENT_ID
    FROM EMPLOYEES
   GROUP BY DEPARTMENT_ID
   HAVING COUNT(*)>5 
 )
AND HIRE_DATE NOT LIKE '%JAN%'
group by department_ID;

此查询返回department_id，由于我按Department_id分组，因此将返回属于每个部门的员工数

This query returns the department_id and because I group by department_id, the count of employees that belong to each department will be returned

输出看起来像这样

  Department_Id | # of Employees
        1             7
        2             6
        4             9

这篇关于明智的员工部门，员工人数超过5名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！