Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00935: group function is nested too deeply (Oracle Acadamy)
ORA-00935: group function is nested too deeply [message #602161] Sat, 30 November 2013 11:51 Go to next message
mjpowers01606
Messages: 2
Registered: November 2013
Location: Holden, MA
Junior Member
Hi All,

New to the board and you all seem great!

I have a problem in solving a homework question. My code is below. Both the sub query and out
query work alone however when I put it all together
I get the ORA-00935: group function is nested too deeply?

I know it is probably a simple error but I am stumped.

Thanks in advance for any help!

Mike

SELECT (O.LAST||', '||O.FIRST) "OFFICER NAME"
FROM OFFICERS O JOIN CRIME_OFFICERS C USING (OFFICER_ID)
GROUP BY (O.LAST||', '||O.FIRST)
HAVING AVG(COUNT(C.CRIME_ID)) > (SELECT AVG(COUNT(CRIME_ID))
FROM CRIME_OFFICERS
GROUP BY (OFFICER_ID));
Re: ORA-00935: group function is nested too deeply [message #602167 is a reply to message #602161] Sat, 30 November 2013 14:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
So what is you are trying to get? It looks like you are trying to get officers who are assigned number of crimes greater than average, right? Then why do you need AVG in HAVING? Also, O.LAST||', '||O.FIRST might be not unique, There could be two officers by name Johh Smith, right? So you need to add officer_id to group by:

SELECT  O.LAST| | ', ' || O.FIRST "OFFICER NAME",
        OFFICER_ID
  FROM      OFFICERS O
        JOIN
           CRIME_OFFICERS C
          USING(OFFICER_ID)
  GROUP BY OFFICER_ID,
        O.LAST || ', ' || O.FIRST
  HAVING COUNT(C.CRIME_ID) > (
                              SELECT  AVG(COUNT(CRIME_ID))
                                FROM CRIME_OFFICERS
                                GROUP BY OFFICER_ID
                             )
/


But this means reading CRIME_OFFICERS twice. It is better to use analytic AVG:

WITH T AS (
           SELECT  O.LAST| | ', ' || O.FIRST "OFFICER NAME",
                   OFFICER_ID,
                   COUNT(C.CRIME_ID) CNT,
                   AVG(COUNT(C.CRIME_ID)) OVER() AVG_CNT
             FROM      OFFICERS O
                   JOIN
                       CRIME_OFFICERS C
                     USING(OFFICER_ID)
             GROUP BY OFFICER_ID,
                      O.LAST || ', ' || O.FIRST
         )
SELECT  "OFFICER NAME",
        OFFICER_ID
  FROM  T
  WHERE CNT > AVG_CNT
/


Now compare plans:

SQL> explain plan for
  2  SELECT  d.deptno,
  3          d.dname
  4    FROM  emp e, dept d
  5    WHERE d.deptno = e.deptno
  6    GROUP BY d.deptno,
  7             d.dname
  8    HAVING COUNT(*) > (
  9                       SELECT  AVG(COUNT(*))
 10                          FROM emp
 11                          GROUP BY deptno
 12                      )
 13  /

Explained.

SQL> set linesize 132
SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 4255261151

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    16 |     7  (29)| 00:00:01 |
|*  1 |  FILTER                        |         |       |       |            |          |
|   2 |   HASH GROUP BY                |         |     1 |    16 |     7  (29)| 00:00:01 |
|   3 |    MERGE JOIN                  |         |    15 |   240 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
|*  6 |     SORT JOIN                  |         |    15 |    45 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL         | EMP     |    15 |    45 |     3   (0)| 00:00:01 |
|   8 |   SORT AGGREGATE               |         |     1 |     3 |     4  (25)| 00:00:01 |
|   9 |    SORT GROUP BY               |         |     1 |     3 |     4  (25)| 00:00:01 |
|  10 |     TABLE ACCESS FULL          | EMP     |    15 |    45 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(*)> (SELECT AVG(COUNT(*)) FROM "EMP" "EMP" GROUP BY "DEPTNO"))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
   6 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")

24 rows selected.

SQL> explain plan for
  2  WITH T AS (
  3             SELECT  d.dname,
  4                     deptno,
  5                     COUNT(*) CNT,
  6                     AVG(COUNT(*)) OVER() AVG_CNT
  7               FROM      emp e
  8                     JOIN
  9                         dept d
 10                       USING(deptno)
 11               GROUP BY d.dname,
 12                        deptno
 13           )
 14  SELECT  dname,
 15          deptno
 16    FROM  T
 17    WHERE CNT > AVG_CNT
 18  /

Explained.

SQL> set linesize 132
SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 1504237389

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |     9 |   432 |     7  (29)| 00:00:01 |
|*  1 |  VIEW                           |         |     9 |   432 |     7  (29)| 00:00:01 |
|   2 |   WINDOW BUFFER                 |         |     9 |   144 |     7  (29)| 00:00:01 |
|   3 |    HASH GROUP BY                |         |     9 |   144 |     7  (29)| 00:00:01 |
|   4 |     MERGE JOIN                  |         |    15 |   240 |     6  (17)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
|   6 |       INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  7 |      SORT JOIN                  |         |    15 |    45 |     4  (25)| 00:00:01 |
|   8 |       TABLE ACCESS FULL         | EMP     |    15 |    45 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CNT">"AVG_CNT")
   7 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")

22 rows selected.

SQL>


SY.
Re: ORA-00935: group function is nested too deeply [message #602212 is a reply to message #602167] Sun, 01 December 2013 10:20 Go to previous message
mjpowers01606
Messages: 2
Registered: November 2013
Location: Holden, MA
Junior Member
Thanks SY! In future requests for help I will be sure to let all of you know what it is I am trying to figure out!

Mike
Previous Topic: Please help with multiple update
Next Topic: ORGANIZATION_CODE must be declared
Goto Forum:
  


Current Time: Wed Oct 22 23:41:08 CDT 2014

Total time taken to generate the page: 0.13205 seconds