By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

problem with joins and aggregate functions

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT     Recipes.Id, Recipes.RecipeName, Recipes.DateAdded, Recipes.Show, Recipes.Category, Recipes.ShortDescription, Recipes.PrepareTime_Secs, 
  2.                       Recipes.Level, RegisteredUsers.Username AS Uploader, AVG(Rate.Rating) AS r1, SUM(Rate.Rating) AS r2
  3.  
  4. FROM         Recipes INNER JOIN
  5.                       RegisteredUsers ON Recipes.Uploader = RegisteredUsers.Id INNER JOIN
  6.                           (SELECT     Id AS rateId,  Rating
  7.                             FROM          RecipeRating
  8.                             GROUP BY rateId
  9.                             ) AS Rate ON Recipes.Id =Rate.RecipeId
  10. WHERE     (Recipes.RecipeName = @input)
  11.  
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.
Apr 6 '09 #1
Share this Question
Share on Google+
4 Replies


code green
Expert 100+
P: 1,726
Not familiar with the error message but pretty sure you cannot use an alias within a GROUP BY
Apr 7 '09 #2

P: 2
ok,
i changed the query as follows:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT     Recipes.Id, Recipes.RecipeName, Recipes.DateAdded, Recipes.Show, Recipes.Category, Recipes.ShortDescription, Recipes.PrepareTime_Secs, 
  3.                       Recipes.Level, RegisteredUsers.Username AS Uploader, AVG(Rate.Rating) AS r1, SUM(Rate.Rating) AS r2
  4. FROM         Recipes INNER JOIN
  5.                       RegisteredUsers ON Recipes.Uploader = RegisteredUsers.Id INNER JOIN
  6.                           (SELECT     Id, RecipeId, Rating
  7.                             FROM          RecipeRating
  8.                             GROUP BY RecipeId) AS Rate ON Recipes.Id = Rate.RecipeId
  9. WHERE     (Recipes.RecipeName = @input)
  10.  
  11.  
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..
Apr 7 '09 #3

code green
Expert 100+
P: 1,726
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.
Apr 7 '09 #4

ck9663
Expert 2.5K+
P: 2,878
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
Apr 7 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.