sql query help [message #644517] |
Sun, 08 November 2015 23:44 |
|
arifs3738
Messages: 39 Registered: November 2015 Location: India
|
Member |
|
|
Hi All,
Question:
List the details about employees who have maximum number of people reporting to them?
SQL>
SELECT E.MGR, COUNT (*) FROM EMP E
GROUP BY E.MGR
HAVING COUNT (*) = (SELECT MAX (COUNT (*)) FROM EMP M GROUP BY M.MGR);
I am getting the o/p as below:
MGR COUNT(*)
7698 5
But i want to see all the employees data reporting to manager with manager data and count, not able to get that....pls help on this!!!
|
|
|
|
|
Re: sql query help [message #644525 is a reply to message #644523] |
Mon, 09 November 2015 01:59 |
|
Michel Cadot
Messages: 68617 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Another way which scans only once the base table:
SQL> with s1 as (select emp.*, count(*) over (partition by mgr) cnt from scott.emp),
2 s2 as (select s1.*, max(cnt) over() maxcnt from s1)
3 select *
4 from s2
5 where cnt = maxcnt
6 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CNT MAXCNT
---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-1981 950 30 5 5
7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30 5 5
7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30 5 5
7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30 5 5
7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30 5 5
[Updated on: Mon, 09 November 2015 02:01] Report message to a moderator
|
|
|
|
Re: sql query help [message #644543 is a reply to message #644525] |
Mon, 09 November 2015 06:43 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 09 November 2015 02:59
Another way which scans only once the base table:
Except your query isn't equivalent to OP's query. OP wants details about employees who have maximum number of people reporting to them (in other word about managers who have most immediate subordinates) while your query returns subordinates of such managers.
SY.
|
|
|
Re: sql query help [message #644544 is a reply to message #644543] |
Mon, 09 November 2015 07:28 |
|
Michel Cadot
Messages: 68617 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I took OP's solution query and output as input.
The condition "ON D.MGR =A.MGR" seems to indicate OP wants the subordinates, otherwise the condition would be "ON D.MGR =A.EMPNO".
[Updated on: Mon, 09 November 2015 07:29] Report message to a moderator
|
|
|