Geek Answers Handbook

Get the number of employees who worked in several departments with SQL query

I am trying to figure out a query that shows the number of the number (number) of employees who worked in more than 1 department. Here is the name and fields of the table:

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

Assume the contents of the Department_Employee table

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 the right request, the result should be equal to 2 (employees), since 2 employees work in several departments.

  • Employee 0001 works in 3 departments
  • 0002 employee works in 2 departments


I tried the following query

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

But the result is incorrect.

Please help me.

Thanks.

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

:

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

:

;
WITH cte As
(
    SELECT id_employee
    FROM Department_Employee
    GROUP BY id_employee
    HAVING COUNT(*)>1
)
SELECT COUNT(*) FROM cte
+3
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 try first to try to get the id group id_employee division count and since our requirement is to find an employee who belongs to more than one department using the counter (e.id_dep)> 1 to get the same.

0
source

More articles: