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

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 COUNT(*)
FROM
(
SELECT id_employee, COUNT(*) AS CNT
FROM Department_Employee
GROUP BY id_employee
) AS T
WHERE CNT > 1