Results 1 to 4 of 4

Thread: AVG Query ?

  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Post 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
    );

  2. #2
    Join Date
    Dec 2007
    Posts
    2

    Smile subquery

    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

  3. #3
    Join Date
    Dec 2007
    Posts
    2
    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.......

  4. #4
    Join Date
    Dec 2007
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •