#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    45
    Rep Power
    12

    LEFT JOIN with AVG()


    This is my query:

    select b.bookID,b.title,b.author,b.connection,AVG(r.score) from books b LEFT JOIN book_ratings r on b.bookID=r.bookID group by r.bookID,b.bookID,b.title,b.author,b.connection order by UPPER(title);

    I get the error:
    Attribute unnamed_join.title must be GROUPed or used in an aggregate function

    It works fine if I use a silent inner join, or if I replace the AVG with another field from that table and remove the GROUP BY clause.

    Help? I don't understand the error given that b.title is part of the GROUP.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Presumably the problem is that you have order by on something that is not part of select clause in conjunction with group by. Use upper(title) in select clause and group by clause.

    If postgres does not support expressions in group by you need to use a derived table (where you have the upper invocation) and group on that.
    Last edited by swampBoogie; February 24th, 2003 at 11:15 AM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    45
    Rep Power
    12
    When I read your reply I realized that I had been completely ignoring the UPPER(title) part of the query, and that it needed to be UPPER(b.title).

    Doh! It's working great now; thanks for the help.

IMN logo majestic logo threadwatch logo seochat tools logo