problem with joins and aggregate functions

[Tomerz]

Hi all, and thanks ahead to the helpers.
for several hours im trying to overcome on an error im stuck with..
that's the issue:
i have 3 table:
1. Recipes
2. RegisteredUsers
3. RecipeRating

the searching value would be Recipes.RecipeName.
i would like to retrieve the following:
1. Recipes.*
2. RegisteredUsers.Username
3. AVG(Rating) , SUM(Rating)

that's the query i've tried to run:

SELECT     Recipes.Id, Recipes.RecipeName, Recipes.DateAdded, Recipes.Show, Recipes.Category, Recipes.ShortDescription, Recipes.PrepareTime_Secs, 
                      Recipes.Level, RegisteredUsers.Username AS Uploader, AVG(Rate.Rating) AS r1, SUM(Rate.Rating) AS r2
 
FROM         Recipes INNER JOIN
                      RegisteredUsers ON Recipes.Uploader = RegisteredUsers.Id INNER JOIN
                          (SELECT     Id AS rateId,  Rating
                            FROM          RecipeRating
                            GROUP BY rateId
                            ) AS Rate ON Recipes.Id =Rate.RecipeId
WHERE     (Recipes.RecipeName = @input)
 

however, i keep getting the same annoying error: The multi-part identifier "Rate.rateId" could not be bound.
i tried several variety of changes but nothing much helped.

[code green]

Not familiar with the error message but pretty sure you cannot use an alias within a GROUP BY

[Tomerz]

ok,
i changed the query as follows:

 
SELECT     Recipes.Id, Recipes.RecipeName, Recipes.DateAdded, Recipes.Show, Recipes.Category, Recipes.ShortDescription, Recipes.PrepareTime_Secs, 
                      Recipes.Level, RegisteredUsers.Username AS Uploader, AVG(Rate.Rating) AS r1, SUM(Rate.Rating) AS r2
FROM         Recipes INNER JOIN
                      RegisteredUsers ON Recipes.Uploader = RegisteredUsers.Id INNER JOIN
                          (SELECT     Id, RecipeId, Rating
                            FROM          RecipeRating
                            GROUP BY RecipeId) AS Rate ON Recipes.Id = Rate.RecipeId
WHERE     (Recipes.RecipeName = @input)
 
 

im receiving the below error:
column 'RecipeRating.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause..

when i add it to the 'group by' clause, i get the same message for 'Rating'.
when i add 'Rating' to the clause, im receiving the error message for 'Recipes.Id'

so i've added 'GROUP BY Recipes.Id' to the outer table, and the same message appears for the 'Recipes.RecipeName'

it doesn't look like it gonna end..

[code green]

Yup,that is how it is.
If one field is aggregated or in a GROUP BY then all fields must comply.
I have studied the reasoning but would not attempt to explain it here.
I have tried ways around but have not been successful.
MySql for example lets you get away with it, but this is simply the engine
allowing short-hand.

[ck9663]

On your first post, you have this portion "GROUP BY rateId". This is wrong because you don't really have a column named rateId. It's an alias. You can not use alias in any other part of the query, including WHERE, GROUP BY, ORDER...

On your second post it's because you have to include Rating on your group by.

The question, however, is why do you need it grouped? If you're concerned with duplicate, just use DISTINCT.

--- CK