CODEWITHSUNDEEP
sqloracle-databaseaveragehaving
user8949199
user8949199

Reputation:

ORA-30483: window functions are not allowed here?

I need a list of movies with a certain average rating to show up. I know that the problem is with the HAVING clause, but I'm not sure as to how to fix it. Without that clause it works, and it lists all movie/actor combos that follow the specified qualities, but I need them to also have an average rating above 4 (which is what I'm trying to do with the having clause).

With the HAVING clause it says ORA-30483: window functions are not allowed here. Anybody know how I can fix this?

SELECT DISTINCT m.title "Movie Title",
   ROUND(AVG(r.rating) OVER (PARTITION BY m.movieid, a.actorid),2) "Average Rating",
   a.afname||' '||a.alname "Name",
   m.ryear
FROM netflix.actors a JOIN netflix.movies_actors ma
   ON a.actorid = ma.actorid
JOIN netflix.movies m
   ON ma.movieid = m.movieid
JOIN netflix.ratings100 r
   ON m.movieid = r.movieid
JOIN netflix.movies_genres g
   ON m.movieid = g.movieid
WHERE a.gender LIKE 'F'
   AND g.genrecode LIKE 'DRM'
   AND m.ryear >= TO_CHAR(SYSDATE, 'yyyy')-12
HAVING AVG(r.rating) OVER (PARTITION BY m.movieid, a.actorid) >= 4
ORDER BY "Average Rating" desc

Upvotes: 0

Views: 3835

Answers (2)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Analytics functions can't use in WHERE or HAVING bloks. They make calculations for the resultset in SELECT-block. And also you use HAVING without GROUP BY it's an error, too. I think you can use a subquery and WHERE for it.

SELECT *
FROM
  (
    SELECT DISTINCT m.title "Movie Title",
       ROUND(AVG(r.rating) OVER (PARTITION BY m.movieid, a.actorid),2) "Average Rating",
       a.afname||' '||a.alname "Name",
       m.ryear
    FROM netflix.actors a JOIN netflix.movies_actors ma
       ON a.actorid = ma.actorid
    JOIN netflix.movies m
       ON ma.movieid = m.movieid
    JOIN netflix.ratings100 r
       ON m.movieid = r.movieid
    JOIN netflix.movies_genres g
       ON m.movieid = g.movieid
    WHERE a.gender LIKE 'F'
       AND g.genrecode LIKE 'DRM'
       AND m.ryear >= TO_CHAR(SYSDATE, 'yyyy')-12
  )
WHERE "Average Rating" >= 4
ORDER BY "Average Rating" desc

Upvotes: 1

Nandish B
Nandish B

Reputation: 212

windowing or analytic function are allowed only in select statements. so you can filter like this:

    select * from (
    SELECT DISTINCT m.title "Movie Title",
       ROUND(AVG(r.rating) OVER (PARTITION BY m.movieid, a.actorid),2) "Average Rating",
       a.afname||' '||a.alname "Name",
       m.ryear
    FROM netflix.actors a JOIN netflix.movies_actors ma
       ON a.actorid = ma.actorid
    JOIN netflix.movies m
       ON ma.movieid = m.movieid
    JOIN netflix.ratings100 r
       ON m.movieid = r.movieid
    JOIN netflix.movies_genres g
       ON m.movieid = g.movieid
    WHERE a.gender LIKE 'F'
       AND g.genrecode LIKE 'DRM'
       AND m.ryear >= TO_CHAR(SYSDATE, 'yyyy')-12


    ) as f1
    where f1.[Average Rating] >=4
ORDER BY f1.[Average Rating] desc

Upvotes: 0

Related Questions