| ORA-00935: group function is nested too deeply [message #602161] |
Sat, 30 November 2013 11:51  |
 |
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  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
