I'm not sure what SQL Platform you're using or what your schema exactly like... i can only infer based on your queries. If you're doing a Query of Query i don't think CF supports advanced functionality like HAVING... but in SQL it would be something like this:
SELECT RecipeID, count(recipereviewID) as totalratings, avg(ratingvalue) as avgrating FROM dbo.recipes INNER JOIN dbo.relrecipecats ON dbo.recipes.recipeID = dbo.relrecipecats.recipeID INNER JOIN dbo.recipecategories ON dbo.relrecipecats.recipecategoryID = dbo.recipecategories.recipecategoryID INNER JOIN dbo.recipereviews ON dbo.recipes.recipeID = dbo.recipereviews.recipeID INNER JOIN dbo.ratings ON dbo.ratings.ratingID = dbo.recipereviews.ratingID INNER JOIN dbo.images ON dbo.recipes.imageID = dbo.images.imageID WHERE recipes.imageID <> 0 AND recipereviews.ratingID >= 4 AND recipes.addressid = 0 AND (MONTH(datecreated) = MONTH(GETDATE())) GROUP BY RecipeID HAVING AVG(ratingvalue) >= 4 ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Thursday, November 02, 2006 1:17 AM Subject: CFQUERY - Aggregate Function > Can any recommend a free forum like houseoffusion for SQL coding issues. > I've used sqlmag.com but they forums have little traffic. > >>From the 2nd query, I'm trying to extract only records from 2nd query where >>the average of avg(ratingvalue) >=4 and I'm not sure how do do this. > > 1st Query > SELECT * > FROM dbo.recipes INNER JOIN > dbo.relrecipecats ON dbo.recipes.recipeID = > dbo.relrecipecats.recipeID INNER JOIN > dbo.recipecategories ON > dbo.relrecipecats.recipecategoryID = dbo.recipecategories.recipecategoryID > INNER JOIN > dbo.recipereviews ON dbo.recipes.recipeID = > dbo.recipereviews.recipeID INNER JOIN > dbo.ratings ON dbo.ratings.ratingID = > dbo.recipereviews.ratingID INNER JOIN > dbo.images ON dbo.recipes.imageID = dbo.images.imageID > WHERE recipes.imageID<>0 And recipereviews.ratingID >=4 AND > recipes.addressid = 0 AND (MONTH(datecreated) = MONTH(GETDATE())) > > 2nd Query > > SELECT count(recipereviewID) AS totalratings, avg(ratingvalue) AS avgrating > FROM recipereviews INNER JOIN ratings ON recipereviews.ratingID = > ratings.ratingID > WHERE recipeID = #whatscookin.recipeID# > (I wanted to add to this with ) AND (avgrating) >= 4 > (but I get an error, invalid column name avgrating) > (I've also tried) > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:258739 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4