CODEWITHSUNDEEP
sqlsqlitesyntax-error
Olly
Olly

Reputation: 35

Cannot filter null even has implemented in where function

Three tables:-Rating ( rID, mID, stars, ratingDate ) Movie ( mID, title, year, director )Reviewer ( rID, name )

Find the difference between the average rating of movies released before 1980 and the average rating of movies released after 1980.

(Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)

But there is always null,even I filtered the null in where function

''''
select r.mID,sum(stars),
count(case when year>1980 and stars is not null then stars end )as "numaf",
count(case when year<1980 and stars is not null then stars end )as "numbf",
sum(case when year>1980 and stars is not null then stars end )as "sumaf",
sum(case when year<1980 and stars is not null then stars end )as "sumbf",
(("sumaf"/"numaf")-("sumbf"/"numbf")) as "difof"
from Rating r left join movie m on r.mID = m.mID 
where stars is not null
group by r.mID
''''

and this is output, always has "NULL":
101 9   0   3   <NULL>  9   <NULL>
103 5   0   2   <NULL>  5   <NULL>
104 5   2   0   5   <NULL>  <NULL>
106 9   0   2   <NULL>  9   <NULL>
107 8   2   0   8   <NULL>  <NULL>
108 10  3   0   10  <NULL>  <NULL>

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

The average rating for movies is:

select r.mID, avg(stars) as avg_stars
from Rating r 
group by r.mID;

Then, to answer the question, you can join this to movies and use conditional aggregation:

select avg(case when year < 1980 then avg_stars) as avg_stars_pre1980,
       avg(case when year > 1980 then avg_stars) as avg_stars_post1980,
       (avg(case when year < 1980 then avg_stars) -
        avg(case when year > 1980 then avg_stars) a
       ) as diff       
from (select r.mID, avg(stars) as avg_stars
      from Rating r 
      group by r.mID
     ) r join
     movie m
     on r.mID = m.mID 
group by r.mID;

The reason you are getting NULL is because you think you are dividing column aliases defined in the SELECT. However, you are really dividing strings. So:

(("sumaf"/"numaf")-("sumbf"/"numbf")) as "difof"

is merely:

(('sumaf'/'numaf')-('sumbf'/'numbf')) as difof

Strings are interpreted as numbers, in a numeric constant, with the leading digits converted to a number. These have no leading digits, so the values are all 0 -- resulting in a divide-by-zero. MySQL returns NULL for divide by zero rather than generating an error.

Upvotes: 1

FizzBuzz
FizzBuzz

Reputation: 693

To get the Avg rating of movies split out by before and after 1980, you can do something like:

SELECT SUM(avgRating)/Count(mID), yearSection
FROM (
    SELECT 
       r.mid,
       round(sum(stars),2)/count(r.mID) as avgRating,
       case when year > 1980 then 1 else 0 end as yearSection
from Rating r left join movie m on r.mID = m.mID 
where stars is not null
group by r.mID
     )AS foo
GROUP BY yearSection

This won't result in any nulls. Also you should need to round the sum as a decimal, or else your averages won't be correct.

Upvotes: 1

Related Questions