Help getting the total count of a column with join

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

Help getting the total count of a column with join

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

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

Hello.

In a table called "WiseTopic_MP3" there is a column called "Listens" (int) and "songId" (pk)(int).

In a second table called "WiseTopic_Artist_Album_Songs" with the columns "albumId" (int) and "SongId" (fk)(int)

I want to get the total number of "Listens" for an album, which is the total number of all the listens for each "Song" with the associated "AlbumId".

I guess I need to use SUM and a JOIN? Please help me get my head around this.

Here is my existing query; can you help me work this into it? See the part of the query that says "-- get listens".

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
/* HELP ME HERE!! */

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

Thank you!

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-03 : 03:30:27

.....


left join
(
select al.albumID, SUM(so.listens) as album_song_listens
from wiseTopic_MP3 so
	join wiseTopic_Artist_Album_Songs al
		on al.songid = so.songid
group by al.albumID
) l
on l.albumID = a.albumID

...and add the count to your select list of course

Em

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2009-03-04 : 12:37:21

Thank you so much elencaster :):)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources