Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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 whose id value is greater than the average

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

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:

SELECT * FROM (SELECT AVG(id) OVER () avgid, t.* FROM tab1 t) WHERE id > avgid;
share|improve this answer
add comment

Well, the short answer is "'Cause Larry says so." ;) but you can do it with a nested select:

select * from tab1 where id > (select avg(id) from tab1)

HTH

share|improve this answer
4  
This wasn't "Larry's decision", this is defined in the SQL standard. –  a_horse_with_no_name Jun 28 '11 at 21:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.