SQL Server 2008 - Conditional Range

Question

Hello,

I have a database that has two tables. These two tables are defined as:

Movie
-----
ID (int)
Title (nvchar)

MovieReview
-----------
ID (int)
MovieID  (int)
StoryRating (decimal)
HumorRating (decimal)
ActingRating (decimal)

I have a stored procedure that allows the user to query movies based on other user's reviews. Currently, I have a temporary table that is populated with the following query:

SELECT
  m.*,
  (SELECT COUNT(ID) FROM MovieReivew r WHERE r.MovieID=m.ID) as 'TotalReviews',
  (SELECT AVG((r.StoryRating + r.HumorRating + r.ActingRating) / 3) 
      FROM MovieReview r WHERE r.MovieID=m.ID) as 'AverageRating'
FROM
  Movie m

In a later query in my procedure, I basically want to say:

SELECT
  *
FROM
  MyTempTable t
WHERE
  t.AverageRating >= @lowestRating AND 
  t.AverageRating <= @highestRating

My problem is, sometimes AverageRating is zero. Because of this, I'm not sure what to do. How do I handle this scenario in SQL?

Answer 1

I'm assuming you want to movies with zero reviews included in your result.

SELECT
  *
FROM
  MyTempTable t
WHERE
  (t.AverageRating >= @lowestRating AND 
  t.AverageRating <= @highestRating)
  OR t.TotalReviews = 0

It's safe to test TotalReviews, because it will never be NULL.

Answer 2

First, I'd used a derived table to calculate the statistics. This makes it easier to convert nulls to zero if you want (although if we're using an Inner Join as I am here, you couldn't need to use Coalesce on the TotalReviews).

Select m.*
    , Coalesce(MovieStats.TotalReviews, 0) As TotalReviews
    , Coalesce(MovieStats.AverageRating, 0) As AverageRating
From Movie As m
    Join    (
                Select R1.MovieId 
                    , Count(*) As TotalReviews
                    , AVG( (r.StoryRating + r.HumorRating + r.ActingRating) / 3 ) As AverageRating
                From MovieReview As r1
                Group By R1.MovieId
                ) As MovieStats
        On MovieStats.MovieId = m.Id
Where MovieStats.AverageRating Between @LowestRating And @HighestRating

The only issue here is that by putting our filter in the Where clause, it means we must have MovieReview records with AverageRating values in given range.

Select m.*
    , Coalesce(MovieStats.TotalReviews, 0) As TotalReviews
    , Coalesce(MovieStats.AverageRating, 0) As AverageRating
From Movie As m
    Left Join   (
                Select R1.MovieId 
                    , Count(*) As TotalReviews
                    , AVG( (r.StoryRating + r.HumorRating + r.ActingRating) / 3 ) As AverageRating
                From MovieReview As r1
                Group By R1.MovieId
                ) As MovieStats
            On MovieStats.MovieId = m.Id
                And MovieStats.AverageRating Between @LowestRating And @HighestRating

This will return zero in those instances where there are no movie ratings or the movie ratings that do exist are outside the passed range.

Yet another possibility is to filter on values that have Reviews and force those to have average ratings in the passed range:

Select m.*
    , Coalesce(MovieStats.TotalReviews, 0) As TotalReviews
    , Coalesce(MovieStats.AverageRating, 0) As AverageRating
From Movie As m
    Left Join   (
                Select R1.MovieId 
                    , Count(*) As TotalReviews
                    , AVG( (r.StoryRating + r.HumorRating + r.ActingRating) / 3 ) As AverageRating
                From MovieReview As r1
                Group By R1.MovieId
                ) As MovieStats
            On MovieStats.MovieId = m.Id
Where MovieStats.MovieId Is Null
    Or ( MovieStats.AverageRating Between @LowestRating And @HighestRating )