SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get a row count using an inner join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shawnmolloy
Yak Posting Veteran

USA
93 Posts

Posted - 04/15/2007 :  04:51:51  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
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)

Singapore
17584 Posts

Posted - 04/15/2007 :  05:04:06  Show Profile  Reply with Quote
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  



KH

Go to Top of Page

shawnmolloy
Yak Posting Veteran

USA
93 Posts

Posted - 04/15/2007 :  05:30:19  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
Cool thanks! That worked great. One quick question if I may; How can I replace the NULL values with "0"?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/15/2007 :  05:36:22  Show Profile  Reply with Quote
"How can I replace the NULL values with "0"?"

SELECT COALESCE(MyColumn, 0)
FROM MyTable
...

Kristen
Go to Top of Page

shawnmolloy
Yak Posting Veteran

USA
93 Posts

Posted - 04/15/2007 :  08:17:49  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000