Aggregate

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:2599
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Jeffry Houser]

  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=17837.14401.6

[Kenneth S. Redler]

On 11/1/06, coldfusion. developer @ att. net coldfusion. developer @ att. net
>
> 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.

> 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#

Try adding this:

HAVING avg(ratingvalue) >= 4

  -- Ken
--
--
Kenneth S. Redler
kredler@...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:2601
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6