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" <[email protected]>
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
