How to find employee department changes?-CodePudding

I have a table that has the old departments and new departments of various employees:

EmployeeID	OldDept	NewDept
123	Design	Design
234	Software	Engineering
345	Design	Software
456	Advertising	Software

How can I create a table that looks like this:

Dept	TransfersIn	TransfersOut
Software	2	1
Design	1	2
Advertising	0	0
Engineering	1	1

CodePudding user response：

You might be able to use this query to find the transfers Out:

SELECT 
    OldDept AS Dept,
    COUNTIF(OldDept != NewDept) As TransfersOut
FROM table
GROUPBY
    EmployeeID,
    OldDept,
    NewDept

CodePudding user response：

Use below

select * from (
  select NewDept as Department, count(*) Transfers, 'TransfersIn' Mode from your_table
  group by Department, Mode
  union all
  select OldDept as Department, count(*) Transfers, 'TransfersOut' Mode from your_table
  group by Department, Mode
)
pivot (sum(Transfers) for mode in ('TransfersIn', 'TransfersOut'))

if applied to sample data in your question - output is

CodePudding user response：

You can use A FUL OUTER JOIN which thw subselects

SELECT 
   COALESCE(OldDept,NewDept) AS Dept, IFNULL(TransfersIn,0) AS TransfersIn   , IFNULL(TransfersOut) AS TransfersOut
FROM
(SELECT NewDept, COUNT(*) AS TransfersIn FROM tabl1 GROUP BY NewDept) As Ne
FULL OUTER JOIN 
(SELECT OldDept, COUNT(*) AS TransfersOut FROM tabl1 GROUP BY NewDept) Ol
ON Ne.NewDept = Ol.OldDept