Hello -
I am trying to display the average rating for poems that have been reviewed.
On he poemOverview page, the poems are rated 1-5. When the form submits
successfully, the poemID, rating, and the current user gets recorded to the
poemRatings table. The user is redirected to the poemDashboard page.
On the poemDashboard page, all poems are listed and called from the poems
table. I want to include a column in the output for the average rating of the
poem:
ID Poem Title Avg. Rating Judge
I am unsure how to created a query that gets the average rating for each poem
to list on the poemDashboard page. I have tried the following query but the
average rating displays for every poem:
<!---Get Poems' Average ratings--->
<cfquery name="qGetRatings" datasource="#request.dsn#"
result="getRatingsResult">
SELECT CAST(AVG(pr.rating) AS numeric(12,2)) AS avgRating
FROM ratings pr
LEFT OUTER JOIN poems p ON pr.poemID = p.id
GROUP BY pr.poemID
</cfquery>
How may I accomplish this?
Thanks,
Adam
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4985
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm