SQL: Simplifying SQL using WITH Clause Part2

Select * From Employee;


For each employee, we want to know how many other people are in their department.
Using an Inline View, we might do the following.


Select E.NAME, D.DEPT_COUNT EMP_DEPT_COUNT
From Employee E,
(Select DEPTID, Count(*) DEPT_COUNT
From Employee
Group By DEPTID) D
Where E.DEPTID=D.DEPTID;


--Using WITH Clause
WITH DEPT_COUNT As
(Select DEPTID, Count(*) DEPT_COUNT
From Employee Group By DEPTID)
Select E.NAME,D.DEPT_COUNT EMP_DEPT_COUNT
From Employee E, DEPT_COUNT D
Where E.DEPTID=D.DEPTID;




What if we also want to pull back each Employees manager name and the number of people in the Managers department?


Select E.NAME, D1.DEPT_COUNT EMP_DEPT_COUNT,
M.NAME Manager_Name,
D2.DEPT_COUNT MGR_DEPT_COUNT
From Employee E,
(Select DEPTID,Count(*) DEPT_COUNT
From Employee Group By DEPTID) D1,
Employee M,
(Select DEPTID, Count(*) DEPT_COUNT
From Employee Group By DEPTID) D2
Where E.DEPTID=D1.DEPTID
And E.ManagerID=M.ID
And M.DEPTID=D2.DEPTID;


WITH DEPT_COUNT As
(Select DEPTID, Count(*) DEPT_COUNT
From Employee Group By DEPTID)
Select E.Name, D1.DEPT_COUNT EMP_DEPT_COUNT,
M.NAME Manager_Name,
D2.DEPT_COUNT MGR_DEPT_COUNT
From
Employee E, DEPT_COUNT D1,Employee M, DEPT_COUNT D2
Where E.DEPTID=D1.DEPTID
And E.ManagerID=M.ID
And M.DEPTID=D2.DEPTID;




Home Page
https://tipsfororacle.blogspot.com/


#GoLearningPoint