List:General Discussion« Previous MessageNext Message »
From:Jason Burfield Date:April 13 2003 5:33am
Subject:ORDER BY with an alias and function fails?
View as plain text  
I recently upgrade to 4.0.12 and I am having an issue with one query.

The query is this:

SELECT s.id, s.title, count(c.book_id) as total_comments,
ifnull(round(( sum(c.rating) / count(c.book_id) ),2), "n/a") as avg "
FROM sponsor_books s
LEFT JOIN book_comments c on s.id=c.book_id ";
GROUP BY s.id 
ORDER BY avg desc, total_comments desc
limit 25";


On 3.23.xx this worked without any problems.

However, now, with 4.0.12 it will return s.id, s.title correctly, but
count(c.book_id) shows up at 0 and the avg fields shows up as NULL (n/a).

If I switch the ORDER BY to: ORDER BY s.id it works fine. If I attempt to
order by 'total_comments' or by 'avg' it fails.

I searched through the list archives and found people having problems with
similar things, but nothing like this.

Am I doing something wrong or is this a bug?

Many thanks!

  --  Jason


Thread
ORDER BY with an alias and function fails?Jason Burfield13 Apr