Query Rating - 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

General SQL Server Forums

New to SQL Server Programming

Query Rating

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-04-10 : 08:30:04

Hi

I have two table

1st Table LP_UserManagement

UserId
Email
Username
Password
UserType
Status

The UserType
0=Admin
1=User
2=Expert

2nd Table Rating

RatingId
UserId
ExpertId
Commends
Points
Status
Deleted

sample datas

UserId Email Username Password UserType Status
1 admin@yahoo.com admin 123456 0 1
2 Sambath@yahoo.com sambath 123456 1 1
3 sathosh@yahoo.com sathosh 123456 1 1
4 Satheesh@yahoo.com Satheesh 123456 2 1

RatingId UserId ExpertId Commends Points Status Deleted
1 2 4 good 4 1 0
2 3 4 good 5 1 0

I have display the record of Expert Rating

please see my test query

select U.Nickname,sum(r.points) as Total, sum(points)/count(U.UserId) as Avgrating from LP_Rating r
join LP_UserManagement U on U.UserId=r.UserId
where r.Deleted=0 group by U.Nickname,r.points

Anybody please advice me.How to get the result

Thanks and Regards
Sambath Kumar

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-10 : 09:40:51

just remove r.points from your GROUP BY


select	U.Username
	,sum(r.points) as Total
	,avg(r.points) as Avgrating
from	LP_Rating r
join	LP_UserManagement U 
		on U.UserId=r.UserId
where	r.Deleted=0 
group by U.Username

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources