group by problem - 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)

group by problem

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-23 : 13:06:56

I am trying list the number of movies rated and the average rating for everyone named Smith. This what i have:

Select count(r.rating) as NumofRatings,avg(r.rating) as AverageRating, c.lname, c.fname
from ratings r
inner join customers c
on c.customerid = r.customerid
where c.lname = 'smith'
group by r.rating;

This only brings up 5 records but there are 91 records of last name smith who have rated movies. The group by seems to be messing the results up can anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-23 : 13:33:13

your statement doesnt make sense. you group by rating and again try to take sum & average. did you mean this?


Select count(r.rating) as NumofRatings,avg(r.rating) as AverageRating, c.lname, c.fname
from ratings r
inner join customers c
on c.customerid = r.customerid
where c.lname = 'smith'
group by c.lname, c.fname

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-23 : 13:45:28

Yeah that was exactly what i needed Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 00:48:50

So i guessed it right
You're Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources