Not familiar with MySQL so this may or may not be relevant.
Generally, everything you are selecting needs to appear in your group by
clause unless it is some kind of aggregation (note you can't have rating
in your select clause as you are averaging it). So it may help to remove
everything from your select except the average rating and the id (you
can put it back later). Also, if your rating is an integer, you may need
to convert it to a floating point number to get an accurate average
(otherwise you may get an integer back).
Here's how it would look in SQL Server...
SELECT AVG(CAST(rating AS float)) AS avgRating,
ShowID
FROM Airdates
GROUP BY ShowID
ORDER BY AVG(CAST(rating AS float)) DESC
I'd be surprised if it was a lot different in MySQL although CAST is
probably different.
-----Original Message-----
From: Les Mizzell [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 9 November 2005 12:31 p.m.
To: CF-Talk
Subject: Re: Difficult Sort - Theory
Matthew Walker wrote:
> Won't this work?
>
> SELECT showId
> FROM airdates
> GROUP BY showId
> ORDER BY AVG(rating)
Hmmm - that's an idea, but doesn't seem to want to work.
Here's the actual query below. (Vastly simplified)
SELECT
Airdates.rating,
Airdates.ShowID,
Shows.Title,
Shows.ShowID
FROM
Shows
INNER JOIN Airdates ON (Shows.ShowID = Airdates.ShowID) WHERE
Shows.title like '%#form.show_title#%'
and Airdates.rating > #form.AMOUNT#
GROUP by Shows.ShowID
ORDER BY Airdates.#form.typeRATE# DESC
It works fine until I try until I try to change the order clause:
ORDER BY AVG(Airdates.rating) DESC
Error: Invalid use of group function
mySQL Database
Other Ideas?
--
-----------
Les Mizzell
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223635
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
