Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
17689 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
22858 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  
 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