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.

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.

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.