Is the second query a query of a query? ( I assume so otherwise I'm not sure why'd you include the first one )
'having' should do it. I do not believe that you can use a "having" clause without also a group by clause, you'll probably want to add the RecipeID to the select list, and then group by it. SELECT recipeID, count(recipereviewID) AS totalratings, avg(ratingvalue) AS avgrating FROM recipereviews INNER JOIN ratings ON recipereviews.ratingID = ratings.ratingID WHERE recipeID = #whatscookin.recipeID# <!--- AND (avgrating) >= 4 gives me error, invalid column name---> group by RecipeID Having avg(ratingvalue) >= 4 If the second query is a QOQ on the first query, then you can probably can bypass the QOQ completely, add the aggregate functions in the first query, add a group by, and the having clause. But, as always it depends on what you're trying to get. At 02:49 PM 11/1/2006, you wrote: >Hello, > > From the 2nd query, I'm trying to extract only records 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# <!--- AND > (avgrating) >= 4 gives me error, invalid column name---> > >D > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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/SQL/message.cfm/messageid:2600 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
