Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    8

    Unanswered: avg function and its output

    Hello
    I guess I'm not understanding something about the avg function and how it's relating to my query. The objective is to find the books in each category that are below the average of the books in the given category.

    When I put this
    SELECT title, category, retail FROM books WHERE (category, retail) IN (SELECT category, avg(retail) FROM books GROUP BY category) ORDER BY category;

    Output:
    HOW TO MANAGE THE MANAGER BUSINESS 31.95
    BODYBUILD IN 10 MINUTES A DAY FITNESS 30.95
    SHORTEST POEMS LITERATURE 39.95
    HOW TO GET FASTER PIZZA SELF HELP 29.95
    4 rows returned in 0.02 seconds

    This is not all of the categories listed.

    This is a plain query with no subqueries. It shows 8 categories (correct)
    SELECT title, category, retail FROM books WHERE (category, retail) IN (SELECT category, avg(retail) FROM books GROUP BY category) ORDER BY category;

    USINESS 31.95
    CHILDREN 34.45
    COMPUTER 52.85
    COOKING 24.35
    FAMILY LIFE 55.975
    FITNESS 30.95
    LITERATURE 39.95
    SELF HELP 29.95
    8 rows returned in 0.01 seconds ''

    I don't understand why the first query is only showing 4 categories.

    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This query returns only books whose price is equal to the average price of their respective categories.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2013
    Posts
    8
    aha..thanks for that! I did forget to change the IN when I was playing with it.

    But it does not like the <ANY OR <all OPERATOR either. To be honest, in this particular case, I'm unsure which one I want to use anyways , any or all.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    WITH r_avg 
         AS (SELECT category, 
                    Avg(retail) RAVG 
             FROM   books 
             GROUP  BY category) 
    SELECT b.title, 
           r_avg.category, 
           b.retail 
    FROM   books b, 
           r_avg 
    WHERE  b.retail < r_avg.ravg 
           AND b.category = r_avg.category 
    
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2013
    Posts
    8
    Thanks! I'll run this through when I get home. I didn't realize you could use aliases in this case. Now it appears to make sense

Tags for this Thread

Posting Permissions

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