database - Passing values into CASE statement -
and thank in advance help.
i'm trying take results 2 separate queries , include them in third query has case statement. i've had success i'm not able present results of third query in proper order. purpose of show employee count each department under different managers. far can load separately manager names , departments , employee department count totals department. can't figure out how manager names in , employee department count in each manager row. below 2 source queries i've used far , query case statement. i've looked @ unpivot function no success yet.
a) simple query lists each primary manager name. there sub managers returned using hierarchy query later.
select name employees "boss" employeeid in (‘1’,'5','25','84','85'); b) query returns department id count each main manager (‘1’,'5','25','84','85') sub-managers.
select departmentid, count(departmentid) count employees departmentid = departmentid , level <= 3 connect prior employeeid = bossid start employeeid = 5 group departmentid order departmentid; c) here’s case statement outputs desired. problem here select statement outputs manager names , manager departments columns. need output both manager names , manager's employee department counts individual manager row columns. i've tried separate select of manager names ‘boss’ column , select include department counts. got messy. passing counts in second statement create additional unwanted column.
select e.name "boss", count(case when d.departmentid = '1' 1 end) "finance", count(case when d.departmentid = '2' 1 end) "hr", count(case when d.departmentid = '3' 1 end) "it", count(case when d.departmentid = '4' 1 end) "marketing", count(case when d.departmentid = '5' 1 end) "sales" employees e, departments d e.employeeid in (select distinct e.bossid employees e) , e.departmentid = d.departmentid (+) group e.name order e.name; boss finance hr marketing sales
-------------------- ---------- ---------- ---------- ---------- ----------
baxter carney 0 0 0 0 1
blythe pierce 0 0 0 0 1
here's altered case query loads employee department counts unfortunately loads department , not individual manager. problem i'm stuck on right now. how pass counts right manager , right column.
select departmentid "deptno", count(case when departmentid = '1' 1 end) "finance", count(case when departmentid = '2' 1 end) "hr", count(case when departmentid = '3' 1 end) "it", count(case when departmentid = '4' 1 end) "marketing", count(case when departmentid = '5' 1 end) "sales" employees departmentid = departmentid , level <= 3 connect prior employeeid = bossid start employeeid = 5 group departmentid order departmentid /
deptno finance hr marketing sales 3 0 0 1 0 0 5 0 0 0 0 21 and here's managers. can see keeps increasing individual department count.
deptno finance hr marketing sales 1 4 0 0 0 0 2 0 23 0 0 0 3 0 0 20 0 0 4 0 0 0 1 0 5 0 0 0 0 28