SQL Server Get number of employees who worked in more than one department with SQL query

insrf1ej 于 10个月前发布在其他

关注(0)|答案(5)|浏览(110)

I'm trying to figure out a query which show the number (amount) of employees who worked in more than 1 departments. Here the table name and fields:

Employee (id_employee, employee_name, salary)
Department (id_dept, dept_name, budget)
Department_Employee (id_employee, id_dept, workhours_percentage)

Suppose the content of Department_Employee table is

id_employee  id_dept  workhours_percentage
-----------  -------  --------------------
0001           03         100
0001           04          50
0001           05          60
0002           05          60
0002           09          90
0003           08          80
0004           07          80
0005           06          60
0006           05          70
0007           04          75
0008           10          95
0009           02          25
0010           01          40

With a right query, the result should be 2 (employees), because there are 2 employees who work in more than one department

Employee 0001 work in 3 departments
Employee 0002 work in 2 departments

I tried with the following query

SELECT COUNT(DISTINCT id_employee)
FROM Department_Employee
GROUP BY id_employee
HAVING COUNT(id_employee)>1

But the result isn't right.

Please help me out.

Thanks.

sql-server

来源：https://stackoverflow.com/questions/19608325/get-number-of-employees-who-worked-in-more-than-one-department-with-sql-query

5条答案

按热度按时间

xam8gpfp1#

SELECT COUNT(*)
FROM
(
SELECT id_employee, COUNT(*) AS CNT
FROM Department_Employee
GROUP BY id_employee
) AS T
WHERE CNT > 1

赞(0）回复(0）举报 10个月前

3z6pesqy2#

To get all employees that work in more than one department:

SELECT id_employee, COUNT(*)
FROM Department_Employee
GROUP BY id_employee
HAVING COUNT(*)>1

To count them:

;
WITH cte As
(
    SELECT id_employee
    FROM Department_Employee
    GROUP BY id_employee
    HAVING COUNT(*)>1
)
SELECT COUNT(*) FROM cte

赞(0）回复(0）举报 10个月前

inb24sb23#

select e.id_employee, count(e.id_dep) from Department_Employee e group by e.id_employee having count(e.id_dep)>1 ;

In this I am trying to first trying to get a count of dept id group by id_employee and as our requirement is to find the employee who belongs to more than one department using having count(e.id_dep)>1 to retrieve the same.

赞(0）回复(0）举报 10个月前

qv7cva1a4#

SELECT COUNT(*)
FROM (
  SELECT id_employee, COUNT(*) AS CNT
  FROM Department_Employee
  GROUP BY id_employee
) AS T
WHERE CNT > 1

赞(0）回复(0）举报 10个月前

cxfofazt5#

SELECT name FROM EMPLOYEE
WHERE  (SELECT count(dept) as empcount ) > 1

赞(0）回复(0）举报 10个月前

我来回答

SQL Server Get number of employees who worked in more than one department with SQL query

5条答案

相关问题

热门标签

最新问答