Home » SQL & PL/SQL » SQL & PL/SQL » sql query help (Oracle 11g)
sql query help [message #644517] Sun, 08 November 2015 23:44 Go to next message
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 #644519 is a reply to message #644517] Mon, 09 November 2015 00:20 Go to previous messageGo to next message
bugfox
Messages: 18
Registered: October 2010
Junior Member
this one ?
SELECT E.MGR, COUNT (*) FROM EMP E
GROUP BY E.MGR
Re: sql query help [message #644523 is a reply to message #644519] Mon, 09 November 2015 00:56 Go to previous messageGo to next message
arifs3738
Messages: 39
Registered: November 2015
Location: India
Member
HI All,

I got the answers thru Facebook Oracle group....Thanks for all your help!!!

Below is the query:

SQL:
SELECT A.* , D.MGR, D.COUNT FROM EMP A
INNER JOIN 
(SELECT E.MGR AS MGR, COUNT (*) AS COUNT FROM EMP E
GROUP BY E.MGR
HAVING COUNT (*) = (SELECT MAX (COUNT (*)) FROM EMP M GROUP BY M.MGR)) D
ON D.MGR =A.MGR;


OUTPUT:
7499	ALLEN	SALESMAN	7698	20-FEB-1981 00:00:00	1600	300	30	7698	5
7521	WARD	SALESMAN	7698	22-FEB-1981 00:00:00	1250	500	30	7698	5
7654	MARTIN	SALESMAN	7698	28-SEP-1981 00:00:00	1250	1400	30	7698	5
7844	TURNER	SALESMAN	7698	08-SEP-1981 00:00:00	1500	0	30	7698	5
7900	JAMES	CLERK	        7698	03-DEC-1981 00:00:00	950		30	7698	5
Re: sql query help [message #644525 is a reply to message #644523] Mon, 09 November 2015 01:59 Go to previous messageGo to next message
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 #644527 is a reply to message #644525] Mon, 09 November 2015 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See the differences in the execution plans.
Your query:
-------------------------------------------------------------
| Id  | Operation              | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      2 |     8  (25)|
|*  1 |  HASH JOIN             |      |      2 |     8  (25)|
|   2 |   VIEW                 |      |      1 |     4  (25)|
|*  3 |    FILTER              |      |        |            |
|   4 |     HASH GROUP BY      |      |      1 |     4  (25)|
|   5 |      TABLE ACCESS FULL | EMP  |     16 |     3   (0)|
|   6 |     SORT AGGREGATE     |      |      1 |     4  (25)|
|   7 |      SORT GROUP BY     |      |      1 |     4  (25)|
|   8 |       TABLE ACCESS FULL| EMP  |     16 |     3   (0)|
|*  9 |   TABLE ACCESS FULL    | EMP  |     13 |     3   (0)|
-------------------------------------------------------------
Mine:
------------------------------------------------------------
| Id  | Operation             | Name | E-Rows | Cost (%CPU)|
------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     14 |     4  (25)|
|*  1 |  VIEW                 |      |     14 |     4  (25)|
|   2 |   WINDOW BUFFER       |      |     14 |     4  (25)|
|   3 |    VIEW               |      |     14 |     4  (25)|
|   4 |     WINDOW SORT       |      |     14 |     4  (25)|
|   5 |      TABLE ACCESS FULL| EMP  |     14 |     3   (0)|
------------------------------------------------------------
Re: sql query help [message #644543 is a reply to message #644525] Mon, 09 November 2015 06:43 Go to previous messageGo to next message
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 Go to previous message
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. Smile

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

Previous Topic: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string
Next Topic: Need Help in SQL Query
Goto Forum:
  


Current Time: Tue Mar 19 00:09:10 CDT 2024