Help with AVG (getting an average)

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)

Help with AVG (getting an average)

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2009-03-02 : 19:50:26

Hi. I'm trying to calculate the average "rating".

There is a table caaled "Artist_Album_Rating" that has an int column "Rating" (value 0-5).

Here is my query. Notice the line that says:

,AVG(r.rating) Rating

This doesn't work. When I add the "Rating" field to the inner join query, i get this error:

Msg 8120, Level 16, State 1, Procedure sp_wisetopic_music_getAlbums, Line 5
Column 'wisetopic_artist_album_ratings.rating' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And here is what I tried that gave me the error:

-- get ratings
left join
(select rating, albumId, count (*) AS RatingCount
from wisetopic_artist_album_ratings
group by albumId) r
on A.albumId = r.albumId

Here is the full query:

select
a.albumid, a.title, a.description, a.dateadded, a.imagesrc
,a.hasimage, a.dateAdded
,b.artistId, b.[name] artistname
,d.genre genre1, e.genre genre2, f.genre genre3
,b.genreid genreID1, b.genreid_2 genreID2, b.genreid_3 genreID3
,ISNULL(cc.CommentCount, 0) CommentCount
,ISNULL(c.SongCount, 0) TrackCount
,ISNULL(r.ratingCount, 0) RatingCount
,AVG(r.rating) Rating
from
wisetopic_artist_album A

-- get soung count
left join
(select albumId, count (*) AS SongCount
from wisetopic_artist_album_songs
group by albumId) C
on A.albumId = C.albumId

-- get comment count
left join
(select albumId, count (*) AS CommentCount
from wisetopic_artist_album_comment
group by albumId) cc
on A.albumId = cc.albumId

-- get ratings
left join
(select albumId, count (*) AS RatingCount
from wisetopic_artist_album_ratings
group by albumId) r
on A.albumId = r.albumId

-- get genre 1
left join
wisetopic_mp3_genre d
on a.genreId1 = d.id

-- get genre 2
left join
wisetopic_mp3_genre e
on a.genreId2 = e.id

-- get genre 3
left join
wisetopic_mp3_genre f
on a.genreId3 = f.id

Thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-02 : 20:21:15

The rule of thumb, or golden rule (or whatever cliche you want to refer to it by) is that, when you have a select statement with some type of aggregates in there (avg, sum, max, count etc.), ALL the non-aggregated columns that are in the select list should be in the group by clause. So, in your example include rating in the group by clause as:

-- get ratings 
left join 
(select rating, albumId, count (*) AS RatingCount 
from wisetopic_artist_album_ratings 
group by albumId,rating) r 
on A.albumId = r.albumId

Now, you might find other errors (probably due to the same reason) in your query, but then, you know how to fix those :-)

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2009-03-02 : 20:26:40

Hey. Thanks for your reply. I see what you're saying about the group by clause. I added the 'rating' column to the group by, now I'm getting a different error.

Msg 8120, Level 16, State 1, Procedure sp_wisetopic_music_getAlbums, Line 5
Column 'wisetopic_artist_album.albumId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't get it, because "AlbumId" is in the group by, so the error is confusing.

Heres the update query:

alter procedure sp_wisetopic_music_getAlbums

as

select
a.albumid, a.title, a.description, a.dateadded, a.imagesrc
,a.hasimage, a.dateAdded
,b.artistId, b.[name] artistname
,d.genre genre1, e.genre genre2, f.genre genre3
,b.genreid genreID1, b.genreid_2 genreID2, b.genreid_3 genreID3
,ISNULL(cc.CommentCount, 0) CommentCount
,ISNULL(c.SongCount, 0) TrackCount
,ISNULL(r.ratingCount, 0) RatingCount, AVG(r.rating) Rating
from
wisetopic_artist_album A

-- get soung count
left join
(select albumId, count (*) AS SongCount
from wisetopic_artist_album_songs
group by albumId) C
on A.albumId = C.albumId

-- get comment count
left join
(select albumId, count (*) AS CommentCount
from wisetopic_artist_album_comment
group by albumId) cc
on A.albumId = cc.albumId

-- get ratings
left join
(select rating, albumId, count (*) AS RatingCount
from wisetopic_artist_album_ratings
group by albumId, rating) r
on A.albumId = r.albumId

-- get artist info
left join
wisetopic_artist b
on a.artistId = b.artistid

-- get genre 1
left join
wisetopic_mp3_genre d
on a.genreId1 = d.id

-- get genre 2
left join
wisetopic_mp3_genre e
on a.genreId2 = e.id

-- get genre 3
left join
wisetopic_mp3_genre f
on a.genreId3 = f.id

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-02 : 20:53:36

This error is coming from the outer query, where, in your select list you have

AVG(r.rating) Rating

Because you have that aggregate function in the select list, everything else in the select list that is not an aggregate must be in a group by clause. So, add a group by clause as follows at the very end of your query:

group by
   a.albumid, a.title, a.description, a.dateadded, a.imagesrc
   ,a.hasimage, a.dateAdded 
   ,b.artistId, b.[name] artistname 
   ,d.genre genre1, e.genre genre2, f.genre genre3 
   ,b.genreid genreID1, b.genreid_2 genreID2, b.genreid_3 genreID3 
   ,ISNULL(cc.CommentCount, 0) 
   ,ISNULL(c.SongCount, 0)
   ,ISNULL(r.ratingCount, 0)

What I described does not necessarily mean that the average you get is correct, or that the logic is correct. So, make sure you look at the results and do sanity checks.

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2009-03-02 : 21:29:16

Hm, that kind of sucks I have to add that huge GROUP BY statement at the end of the query. I always have problems getting my head around the thinking behind GROUP BY.

The problem I'm having now is, I guess, one of the TEXT columns:

Msg 306, Level 16, State 2, Procedure sp_wisetopic_music_getAlbums, Line 5
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Can I not use that datatype in this query? Hm there has to be a solution... I'm not sure what though.

Here's the updated query:

select
a.albumid, a.title, a.description, a.dateadded, a.imagesrc
,a.hasimage, a.dateAdded
,b.artistId, b.[name] artistname
,d.genre genre1, e.genre genre2, f.genre genre3
,b.genreid genreID1, b.genreid_2 genreID2, b.genreid_3 genreID3
,ISNULL(cc.CommentCount, 0) CommentCount
,ISNULL(c.SongCount, 0) TrackCount
,ISNULL(r.ratingCount, 0) RatingCount, AVG(r.rating) Rating
from
wisetopic_artist_album A

-- get soung count
left join
(select albumId, count (*) AS SongCount
from wisetopic_artist_album_songs
group by albumId) C
on A.albumId = C.albumId

-- get comment count
left join
(select albumId, count (*) AS CommentCount
from wisetopic_artist_album_comment
group by albumId) cc
on A.albumId = cc.albumId

-- get ratings
left join
(select rating, albumId, count (*) AS RatingCount
from wisetopic_artist_album_ratings
group by albumId, rating) r
on A.albumId = r.albumId

/*
-- get listens (total of all listens of all MP3s in this album)
left join
(select listens, count (*) AS Listens
from wisetopic_artist_album_ratings
group by albumId, rating) r
on A.albumId = r.albumId
*/

-- get artist info
left join
wisetopic_artist b
on a.artistId = b.artistid

-- get genre 1
left join
wisetopic_mp3_genre d
on a.genreId1 = d.id

-- get genre 2
left join
wisetopic_mp3_genre e
on a.genreId2 = e.id

-- get genre 3
left join
wisetopic_mp3_genre f
on a.genreId3 = f.id

group by
a.albumid, a.title, a.description, a.dateadded, a.imagesrc
,a.hasimage, a.dateAdded
,b.artistId, b.[name]
,d.genre, e.genre, f.genre
,b.genreid , b.genreid_2 , b.genreid_3
,ISNULL(cc.CommentCount, 0)
,ISNULL(c.SongCount, 0)
,ISNULL(r.ratingCount, 0)

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2009-03-04 : 12:01:38

Can someone please help me figure this error out?

Msg 306, Level 16, State 2, Procedure sp_wisetopic_music_getAlbums, Line 5
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.


select
a.albumid, a.title, a.description, a.dateadded, a.imagesrc
,a.hasimage, a.dateAdded
,b.artistId, b.[name] artistname
,d.genre genre1, e.genre genre2, f.genre genre3
,b.genreid genreID1, b.genreid_2 genreID2, b.genreid_3 genreID3
,ISNULL(cc.CommentCount, 0) CommentCount
,ISNULL(c.SongCount, 0) TrackCount
,ISNULL(r.ratingCount, 0) RatingCount, AVG(r.rating) Rating
from
wisetopic_artist_album A

-- get ratings
left join
(select rating, albumId, count (*) AS RatingCount
from wisetopic_artist_album_ratings
group by albumId, rating) r
on A.albumId = r.albumId

/*
-- get listens (total of all listens of all MP3s in this album)
left join
(select listens, count (*) AS Listens
from wisetopic_artist_album_ratings
group by albumId, rating) r
on A.albumId = r.albumId
*/


group by
a.albumid, a.title, a.description, a.dateadded, a.imagesrc
,a.hasimage, a.dateAdded
,b.artistId, b.[name]
,d.genre, e.genre, f.genre
,b.genreid , b.genreid_2 , b.genreid_3
,ISNULL(cc.CommentCount, 0)
,ISNULL(c.SongCount, 0)
,ISNULL(r.ratingCount, 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources