Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to find complete number of reportees under a manager

user10549528Sep 24 2018 — edited Sep 25 2018

Hello gurus,

      SELECT first_name || ' ' ||  last_name,level,

      employee_id,manager_id ,

      LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"

      FROM employees

         START WITH manager_id  is null

         CONNECT BY PRIOR employee_id = manager_id;

I want to find out total number of employees working under King  which includes its immediate descendants plus childeren under that descendants until leaf.

Similarly for Kochhar total number of employees working under Kochhar which includes its immediate descendants plus childeren under that descendants until leaf.

I am trying to do it by sys connect by path , how can i achieve this , please assist

Steven King1100/King
Neena Kochhar2101100   /King/Kochhar
Nancy Greenberg3108101     /King/Kochhar/Greenberg
Daniel Faviet4109108       /King/Kochhar/Greenberg/Faviet
John Chen4110108       /King/Kochhar/Greenberg/Chen
Ismael Sciarra4111108       /King/Kochhar/Greenberg/Sciarra
Jose Manuel Urman4112108       /King/Kochhar/Greenberg/Urman
Luis Popp4113108       /King/Kochhar/Greenberg/Popp
Jennifer Whalen3200101     /King/Kochhar/Whalen
Susan Mavris3203101     /King/Kochhar/Mavris
Hermann Baer3204101     /King/Kochhar/Baer
Shelley Higgins3205101     /King/Kochhar/Higgins
William Gietz4206205       /King/Kochhar/Higgins/Gietz
Lex De Haan2102100   /King/De Haan
Alexander Hunold3103102     /King/De Haan/Hunold
Bruce Ernst4104103       /King/De Haan/Hunold/Ernst
David Austin4105103       /King/De Haan/Hunold/Austin
Valli Pataballa4106103       /King/De Haan/Hunold/Pataballa
Diana Lorentz4107103       /King/De Haan/Hunold/Lorentz
Den Raphaely2114100   /King/Raphaely
Alexander Khoo3115114     /King/Raphaely/Khoo
Shelli Baida3116114     /King/Raphaely/Baida
Sigal Tobias3117114     /King/Raphaely/Tobias
Guy Himuro3118114     /King/Raphaely/Himuro
Karen Colmenares3119114     /King/Raphaely/Colmenares
Matthew Weiss2120100   /King/Weiss
Julia Nayer3125120     /King/Weiss/Nayer
Irene Mikkilineni3126120     /King/Weiss/Mikkilineni
James Landry3127120     /King/Weiss/Landry
Steven Markle3128120     /King/Weiss/Markle
Winston Taylor3180120     /King/Weiss/Taylor
Jean Fleaur3181120     /King/Weiss/Fleaur
Martha Sullivan3182120     /King/Weiss/Sullivan
Girard Geoni3183120     /King/Weiss/Geoni
Adam Fripp2121100   /King/Fripp
Laura Bissot3129121     /King/Fripp/Bissot
Mozhe Atkinson3130121     /King/Fripp/Atkinson
James Marlow3131121     /King/Fripp/Marlow
TJ Olson3132121     /King/Fripp/Olson
Nandita Sarchand3184121     /King/Fripp/Sarchand
Alexis Bull3185121     /King/Fripp/Bull
Julia Dellinger3186121     /King/Fripp/Dellinger
Anthony Cabrio3187121     /King/Fripp/Cabrio
Payam Kaufling2122100   /King/Kaufling
Jason Mallin3133122     /King/Kaufling/Mallin
Michael Rogers3134122     /King/Kaufling/Rogers
Ki Gee3135122     /King/Kaufling/Gee
Hazel Philtanker3136122     /King/Kaufling/Philtanker
Kelly Chung3188122     /King/Kaufling/Chung
Jennifer Dilly3189122     /King/Kaufling/Dilly
Timothy Gates3190122     /King/Kaufling/Gates
Randall Perkins3191122     /King/Kaufling/Perkins
Shanta Vollman2123100   /King/Vollman
Renske Ladwig3137123     /King/Vollman/Ladwig
Stephen Stiles3138123     /King/Vollman/Stiles
John Seo3139123     /King/Vollman/Seo
Joshua Patel3140123     /King/Vollman/Patel
Sarah Bell3192123     /King/Vollman/Bell
Britney Everett3193123     /King/Vollman/Everett
Samuel McCain3194123     /King/Vollman/McCain
Vance Jones3195123     /King/Vollman/Jones
Kevin Mourgos2124100   /King/Mourgos
Trenna Rajs3141124     /King/Mourgos/Rajs
Curtis Davies3142124     /King/Mourgos/Davies
Randall Matos3143124     /King/Mourgos/Matos
Peter Vargas3144124     /King/Mourgos/Vargas
Alana Walsh3196124     /King/Mourgos/Walsh
Kevin Feeney3197124     /King/Mourgos/Feeney
Donald OConnell3198124     /King/Mourgos/OConnell
Douglas Grant3199124     /King/Mourgos/Grant
John Russell2145100   /King/Russell
Peter Tucker3150145     /King/Russell/Tucker
David Bernstein3151145     /King/Russell/Bernstein
Peter Hall3152145     /King/Russell/Hall
Christopher Olsen3153145     /King/Russell/Olsen
Nanette Cambrault3154145     /King/Russell/Cambrault
Oliver Tuvault3155145     /King/Russell/Tuvault
Karen Partners2146100   /King/Partners
Janette King3156146     /King/Partners/King
Patrick Sully3157146     /King/Partners/Sully
Allan McEwen3158146     /King/Partners/McEwen
Lindsey Smith3159146     /King/Partners/Smith
Louise Doran3160146     /King/Partners/Doran
Sarath Sewall3161146     /King/Partners/Sewall
Alberto Errazuriz2147100   /King/Errazuriz
Clara Vishney3162147     /King/Errazuriz/Vishney
Danielle Greene3163147     /King/Errazuriz/Greene
Mattea Marvins3164147     /King/Errazuriz/Marvins
David Lee3165147     /King/Errazuriz/Lee
Sundar Ande3166147     /King/Errazuriz/Ande
Amit Banda3167147     /King/Errazuriz/Banda
Gerald Cambrault2148100   /King/Cambrault
Lisa Ozer3168148     /King/Cambrault/Ozer
Harrison Bloom3169148     /King/Cambrault/Bloom
Tayler Fox3170148     /King/Cambrault/Fox
William Smith3171148     /King/Cambrault/Smith
Elizabeth Bates3172148     /King/Cambrault/Bates
Sundita Kumar3173148     /King/Cambrault/Kumar
Eleni Zlotkey2149100   /King/Zlotkey
Ellen Abel3174149     /King/Zlotkey/Abel
Alyssa Hutton3175149     /King/Zlotkey/Hutton
Jonathon Taylor3176149     /King/Zlotkey/Taylor
Jack Livingston3177149     /King/Zlotkey/Livingston
Kimberely Grant3178149     /King/Zlotkey/Grant
Charles Johnson3179149     /King/Zlotkey/Johnson
Michael Hartstein2201100   /King/Hartstein
Pat Fay3202201     /King/Hartstein/Fay
This post has been answered by L. Fernigrini on Sep 24 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 23 2018
Added on Sep 24 2018
20 comments
1,072 views