Hello, I have a database as such: KEY_ID.........OBJECT_ID.............NUMBER 1......................5........................45 2......................5........................55 3......................4........................32 4......................4........................22 5......................5........................67 ... so on... I want to display the average of the NUMBER values for each OBJECT_ID. For instance, I want to write script that takes all the records with OBJECT_ID = 5, and averages the NUMBER values. Each OBJECT_ID will have it's own webpage, and the average for that specific OBJECT_ID will be displayed on its respective page. Should be relatively simple, I just don't know how to isolate solely the one OBJECT_ID set of numbers to average!
Most db's have an average function. The usual syntax is something like this. Check your database's documentation for more info. --- all objects SELECT OBJECT_ID, AVG(Number) AS AverageNumber FROM YourTable GROUP BY OBJECT_ID --- specific object_id SELECT AVG(Number) AS AverageNumber FROM YourTable WHERE OBJECT_ID = 5
Awesome. Ok, I used the following for each page: SELECT AVG(Number) AS AverageNumber FROM YourTable WHERE OBJECT_ID = 5 Code (markup): And adjusted the OBJECT_ID to correspond to the page of that specific object. But now, I'd like to create a separate page, that have the TOP RANKED averages of all the objects. So, for instance, Say Object 4 has an avg rating of 100, Object 3 has 90, Object 13 has 80, and Object 9 has 65. Say these are the top 4 ranked objects. I'd like it to display as such: Top Rankings: Object 4 ................. 100 Object 3 ................... 90 Object 13 ................. 80 Object 9 ................... 65 I assume i'd be something like: <cfquery name="gettopranks" datasource="mydatasource"> SELECT OBJECT_ID, AVG(Number) AS AverageNumber FROM YourTable GROUP BY OBJECT_ID ORDER BY AverageNumber DEC </cfquery> <cfoutput> #AverageNumber# </cfoutput> Code (markup): Actually, I think that may be wrong (how do you do ascending and descending in coldfusion?). Also, I'd like to only display, say, the top 10 ranked objects. Thanks.
I haven't tested this, but try using the TOP operator to grab the top 10 (5 or however many you want) results. Just use the keyword DESC to grab the results in descending order. <cfquery name="gettopranks" datasource="mydatasource"> SELECT TOP 10 OBJECT_ID, AVG(Number) AS AverageNumber FROM YourTable GROUP BY OBJECT_ID ORDER BY AVG(Number) DESC </cfquery>
I got it to work as such: <body> <cfquery name="gettopranks" datasource="mydatabase"> SELECT TOP 10 RECIPE_ID, AVG(RATE) AS AverageNumber FROM RATE GROUP BY RECIPE_ID ORDER BY AVG(RATE) DESC </cfquery> <cfquery name="gettopranks" datasource="mydatabase"> SELECT TOP 10 RECIPE_ID, AVG(RATE) AS AverageNumber FROM RATE GROUP BY RECIPE_ID ORDER BY AVG(RATE) DESC </cfquery> <cfoutput query = "gettopranks" group="RECIPE_ID"> <tr> <td><span class="style5">#gettopranks.RECIPE_ID#</a></span></td> <td><span class="style5">#gettopranks.AverageNumber#</a></span></td> </tr> </cfoutput> </body> Code (markup): Now... this will return the Recipe ID number and the ranking of the top 10 ranked recipes. The recipe names (associated with these Recipe IDs) are in another table (TBLRECIPES). How can I get this query to retrieve the recipe names associated with these Recipe IDs from the other table? Thanks
Off the top of my head, I think it should just be a simple join. But try it and let me know. The column is called RECIPE_NAME right? SELECT TOP 10 r.RECIPE_ID, r.RECIPE_NAME, AVG(rt.RATE) AS AverageNumber FROM RATE AS rt INNER JOIN TBLRECIPES r ON r.RECIPE_ID = rt.RECIPE_ID GROUP BY r.RECIPE_ID, r.TBLRECIPES ORDER BY AVG(rt.RATE) DESC <cfoutput query = "gettopranks" group="RECIPE_ID"> <tr> <td><span class="style5">#gettopranks.RECIPE_ID#</a></span></td> <td><span class="style5">#gettopranks.RECIPE_NAME#</a></span></td> <td><span class="style5">#gettopranks.AverageNumber#</a></span></td> </tr> </cfoutput> Code (markup):
Unfortunately, this did not work. Here are two of the tables in my database (along with the fields in each): TBLRECIPES: RECIPE_ID, RECIPE_NAME, LINK RATE: RATE_ID, RECIPE_ID, RATE Then, this was the code I used that did not work: <cfquery name="gettopranks" datasource="mydatabase"> SELECT TOP 10 r.RECIPE_ID, r.RECIPE_NAME, AVG(rt.RATE) AS AverageNumber FROM RATE AS rt INNER JOIN TBLRECIPES r ON r.RECIPE_ID = rt.RECIPE_ID GROUP BY r.RECIPE_ID, r.TBLRECIPES ORDER BY AVG(rt.RATE) DESC </cfquery> <cfoutput query = "gettopranks" group="RECIPE_ID"> <tr> <td><span class="style5">#gettopranks.RECIPE_ID#</a></span></td> <td><span class="style5">#gettopranks.RECIPE_NAME#</a></span></td> <td><span class="style5">#gettopranks.AverageNumber#</a></span></td> </tr> </cfoutput> Code (markup): I can't remember what the error message was (I'm not on the same computer - I can check later).
Ah, my bad. I see a typo in the group by. This should work much better. SELECT TOP 10 r.RECIPE_ID, r.RECIPE_NAME, AVG(rt.RATE) AS AverageNumber FROM RATE AS rt INNER JOIN TBLRECIPES r ON r.RECIPE_ID = rt.RECIPE_ID GROUP BY r.RECIPE_ID, [COLOR="Blue"]r.RECIPE_NAME[/COLOR] ORDER BY AVG(rt.RATE) DESC Code (markup):
Why is it, that when I also try to retrieve my LINK field from my TBLRECIPES, I get an error? <cfquery name="gettopranks" datasource="mydatabase"> SELECT TOP 10 r.RECIPE_ID, [COLOR="Red"][B]r.LINK[/B][/COLOR], r.RECIPE_NAME, AVG(rt.RATE) AS AverageNumber FROM RATE AS rt INNER JOIN TBLRECIPES r ON r.RECIPE_ID = rt.RECIPE_ID GROUP BY r.RECIPE_ID, r.RECIPE_NAME ORDER BY AVG(rt.RATE) DESC </cfquery> <cfoutput query = "gettopranks" group="RECIPE_ID"> <tr> <td><span class="style5"><a [COLOR="red"][B]href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>[/B][/COLOR] <td><span class="style5">#gettopranks.AverageNumber#</a></span></td> </tr> </cfoutput> Code (markup):
Its because you're using an aggregate function (ie AVG). Most db's require that any columns not included in the aggregate function(s) must be included in the GROUP BY clause. The database uses the GROUP BY to determine how the results should be summarized. If you omit one or more columns, the db won't know how to calculate the totals. So it throws an error. <cfquery name="gettopranks" datasource="mydatabase"> SELECT TOP 10 [COLOR="Red"][B]r.RECIPE_ID, r.LINK, r.RECIPE_NAME [/B][/COLOR], AVG(rt.RATE) AS AverageNumber FROM RATE AS rt INNER JOIN TBLRECIPES r ON r.RECIPE_ID = rt.RECIPE_ID GROUP BY [COLOR="Red"][B]r.RECIPE_ID, r.LINK, r.RECIPE_NAME [/B][/COLOR] ORDER BY AVG(rt.RATE) DESC </cfquery> Code (markup):