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.
| Author |
Topic |
|
Kotti
Posting Yak Master
129 Posts |
 Posted - 2009-06-02 : 09:44:46
|
| Hi, I am wroking with following query,Here some column rating and avgrating return null value.But i need if rating is null then it return zero.Following is test query.SELECT r.*,rr.noofvoter,rr.Points,rr.avgrating,rr.RatingFROM sf_videomgmt r left JOIN (SELECT VideoId,SUM(Points)AS Points,SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END) AS noofvoter,SUM(Points)/COUNT(VideoId) AS Rating,ISNULL(SUM(Points)*1.0/NULLIF(SUM(CASE WHEN Points=0 THEN 0 ELSE 1 END),0),0) AS avgrating FROM sf_ratingmgmt GROUP BY VideoId) rr ON rr.VideoId=r.VideoId where r.filmtype=1 and r.deleted=0 and r.Status=1 and r.makepublic=1 Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-06-02 : 09:46:55
|
isnull(your_result,0) as your_column_name
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kotti
Posting Yak Master
129 Posts |
Posted - 2009-06-02 : 11:08:10
|
| Yes, It's working.Thanks for your help |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-02 : 11:57:24
|
| i prefer coalesce() because of following reasonshttp://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/04/isnull-or-coalesce.aspx |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-06-02 : 12:19:27
|
In this case (and for my postcounter  ) i prefer isnull.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Select Case null value then return 0