Get number of employees who worked in more than on

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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