SQL> desc tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(1000)
SQL> select * from tab1;
ID NAME
---------- ---------------
1 a
2 b
3 c
SQL> select * from tab1 where id > AVG(id);
select * from tab1 where id > AVG(id)
*
ERROR at line 1:
ORA-00934: group function is not allowed here
SQL>
The error is very clear and says that this cannot be done. But I don't see why. The query makes perfect sense:
select all rows from
tab1
whoseid
value is greater than the average
Best Answer
AVG and other aggregate functions work on sets of data. The WHERE cause does not have access to the entire set, only to data for the row it is operating on. If you created your own AVG function (as a normal function and not a custom aggregate function) it would only be passed one ID value when called from the WHERE clause not the entire set of ID values.
Mezmo's solution will give you your expected results, but if you want to avoid two full table scans (assuming no indexes) you can use a windowing function like this: