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

Reply via email to