AVG Query ?

Hi everyone!

i'm trying to build a query for a movie database (actually it's just an example from a book, but not a real database), that should show me all the movies that have a longer length than the average one.

So far i came till this query here:

Code:

---

select title
 from movie
 group by title
 having length > avg(length) ;

---

which for some reasons isn't right ...

I already searched a lot on the google. In most of the AVG queries at the having clause they compare the avg(length) with some scalar value, which in my case isn't worth doing. There must be some way for comparison of the avg(length) with the actual values of the length, right?
Can anyone please explain me what's wrong with my query, and how exactly should it look like ?

I don't know whether is makes any difference but I'm using Mysql 5.

here is also a small table i created in order to test my query.

Code:

---

create table movie
(
title varchar(20),
year date,
length numeric(4,2),
studioname varchar(20),
producernr int
);

---

hi
you can using a subquery for ave(length)
and then it will compare with main query to get the results.
will catch u in detail after i try to execute it.
bye

hi
i got it right
here is the code
SELECT title
FROM movie
WHERE LENGTH >(SELECT AVG(LENGTH) FROM movie;
there is no need for having/group by
let me know if this is not clear.......

thanks fresher,

it works.
Here is another way for expressing the same thing:

Code:

---

select distinct from movie
where
(
  select avg(length) from movie < length
  group by title
)

---

cheers