Return avg and instance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return avg and instance

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-10-08 : 02:55:04

Hi All,

I have a problematic query which I sure has be answered but am uncertain how to search for it!

SELECT p.productID, p.description, ROUND(AVG(UserRankings.rank*1.0), 2) AS rank
FROM products p LEFT OUTER JOIN	UserRankings ON p.productID = UserRankings.ObjectID
GROUP BY p.productID, p.description

Gives me the avg rank for a product. I would like to pass a User ID to the query in order to get 1) The avg rank as done currently 2) The users own rank. The UserRanking table contains the User ID's that have ranked a product. Can this be done in a single query?

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 03:40:55

Isn't the UserRanking average for multiple users of a given Product?

Or do you want the Product, Average User Ranking, and then all users who ranked that product, plus the ranking they gave?

Kristen

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-10-08 : 04:08:02

Thanks for replaying Kristen,

The user ranking is the average for multiple users. When a user is logged in, I want to show the product, avg ranking for the product AND that user's own ranking!

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 04:45:09

Ah, I see, so a SPECIFIC Ranking for the CURRENT user, and NOT all users that ranked that product.

Probably this:


SELECT p.productID, p.description, ROUND(AVG(UR1.rank*1.0), 2) AS rank, 
	UR2.rank AS MyRanking
FROM products p 
	LEFT OUTER JOIN UserRankings AS UR1
		 ON p.productID = UR1.ObjectID
	LEFT OUTER JOIN UserRankings AS UR2
		 ON p.productID = UR2.ObjectID
		AND UR2.UserID = @MyUserID
GROUP BY p.productID, p.description, UR2.rank

Kristen

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-10-08 : 05:05:41

Thanks Kristen!

As usual the superior knowledge available on SQLTeam wins the day!

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 08:13:12

Ha! If you said that as often as I have pounds in my pocket you'd keep me happy for, oh ... five minutes at least!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources