Get a row count using an inner 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

Get a row count using an inner join

Author

Topic

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-04-15 : 04:51:51

Hello,

I am trying to modify this query, I want to get the count of "reviews" that are related to this song. I added the follow line, but I get this error:

count(review.*) reviews,

Error:

Msg 102, Level 15, State 1, Procedure sp_wisetopic_artist_getAllMusic, Line 12
Incorrect syntax near '*'.

Here is the query:

         
CREATE procedure sp_wisetopic_artist_getAllMusic          
          
@artistId int          
          
as          
          
select 
Mp3.*, 
Artist.[Name] ArtistName, 
Album.*,       
count(review.*) reviews,
Id3.bitRate,      
Id3.frequency,      
Id3.mode,      
dbo.SecToMin(Id3.length) length      
      
from wisetopic_mp3 Mp3          
          
-- get artist info          
left join wisetopic_artist Artist          
on Mp3.userId = Artist.artistId          
          
-- get album info (per song)          
left join wisetopic_artist_album Album          
on Mp3.albumId = Album.albumId          
        
-- get id3 data (length)        
left join wisetopic_mp3_id3 Id3        
on Mp3.mp3Id = Id3.mp3Id          
          
-- get review count
left join wisetopic_artist_song_review review        
on Mp3.mp3Id = review.mp3Id    

where Mp3.UserId = @artistId     
order by Mp3.albumId  
    
--select * from wisetopic_artist_song_review    
--select * from wisetopic_mp3

I also tried this:
Review.count(*) reviews

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-15 : 05:04:06

when you use count(*) the rest of the column in the select statement has to be in the GROUP BY clause.
exmaple


select col1, col2, col3, col4, col5, count(*)
from   sometable
group by col1, col2, col3, col4, col5

Try this changes.


select 
     Mp3.*, 
     Artist.[Name] ArtistName, 
     Album.*,       
     count(review.*) reviews, review.cnt,
     Id3.bitRate,      
     Id3.frequency,      
     Id3.mode,      
     dbo.SecToMin(Id3.length) length           
from wisetopic_mp3 Mp3          
          
-- get artist info          
left join wisetopic_artist Artist          
on Mp3.userId = Artist.artistId          
          
-- get album info (per song)          
left join wisetopic_artist_album Album          
on Mp3.albumId = Album.albumId          
        
-- get id3 data (length)        
left join wisetopic_mp3_id3 Id3        
on Mp3.mp3Id = Id3.mp3Id          
          
-- get review count
left join 
(
    select mp3Id, count(*) as cnt
    from   wisetopic_artist_song_review 
    group by mp3Id
) review
on Mp3.mp3Id = review.mp3Id

where Mp3.UserId = @artistId     
order by Mp3.albumId