stddev and avg of 2 tables oracle-CodePudding

If I have 2 tables in Oracle SQL Developer: movies and movie_types.

Table 'movies' containing values: movie_id, movie_title, movie_qty, movie_value, and movie_cat_id.

Table 'movie_types' containing values: movie_cat_id and movie_category.

How can I display the movie_title, movie_category, and then the movie_qty greater than movie_qty by at least 1 standard dev of all movies' movie_qty?

This is for a homework question, the question itself sort of confuses me and I cannot find a similar example.

Here is the table data:

CREATE TABLE movie
(movie_id number(2),
movie_title varchar2(40),
movie_qty number(2),
movie_value number(2),
movie_cat_id number(2));

CREATE TABLE movie_types
(movie_cat_id number(2),
movie_category varchar2(30));

INSERT INTO movie VALUES(1, 'Alien', 5, 10, 1);
INSERT INTO movie VALUES(2, 'Bladerunner', 3, 8, 1);
INSERT INTO movie VALUES(3, 'Star Wars', 11, 15, 1);
INSERT INTO movie VALUES(4, 'Texas Chainsaw Massacre', 2, 7, 2);
INSERT INTO movie VALUES(5, 'Jaws', 1, 7, 2);
INSERT INTO movie VALUES(6, 'The Good, the Bad, and the Ugly', 2, 7, 3);
INSERT INTO movie VALUES(7, 'Silverado', 1, 7, 3);
INSERT INTO movie VALUES(8, 'Duck Soup', 1, 5, 4);
INSERT INTO movie VALUES(9, 'Planes, Trains, and Automobiles', 3, 5, 4);
INSERT INTO movie VALUES(10, 'Waking Ned Devine', 4, 12, 4);
INSERT INTO movie VALUES(11, 'Deep Blue Sea', 3, 14, 5);
INSERT INTO movie VALUES(12, 'The Fifth Element', 5, 15, 5);

INSERT INTO movie_types VALUES(1, 'SciFi');
INSERT INTO movie_types VALUES(2, 'Horror');
INSERT INTO movie_types VALUES(3, 'Western');
INSERT INTO movie_types VALUES(4, 'Comedy';
INSERT INTO movie_types VALUES(5, 'Drama');

And I have tried:

select movie_category, movie_title,
(avg(movie_qty)),
(stddev(movie_qty))
from movie, movie_type
where movie_type.movie_cat_id = movie.movie_cat_id
group by movie.movie_cat_id;

I know that is wrong, if anyone can offer some guidance I would appreciate it greatly! Thanks in advance let me know if clarification is needed.

CodePudding user response：

As this is homework.

It can be done using analytic functions. Your query is almost there, you would just need to convert the aggregation functions to analytic functions by removing the GROUP BY clause and adding an OVER (...) clause to change it to an analytic function:

SELECT movie_category,
       movie_title,
       movie_qty,
       AVG(movie_qty)    OVER () AS avg_movie_qty,
       STDDEV(movie_qty) OVER () AS stddev_movie_qty
FROM   movie
       INNER JOIN movie_types
       ON movie_types.movie_cat_id = movie.movie_cat_id

Would output the category, title and quantity for each movie and the average and standard deviation OVER a window across the entire result set.

Then you would need to take it a step further and wrap it in an outer query which would filter the rows adding a WHERE clause to compare the quantity to the average plus one standard deviation. I'll leave that for you to complete.

The alternate way of doing it is to take your query, again without the GROUP BY clause and add a filter to compare the quantity:

SELECT movie_category,
       movie_title
FROM   movie
       INNER JOIN movie_types
       ON movie_types.movie_cat_id = movie.movie_cat_id
WHERE  movie_qty > ( ... )

Then, in the braces you can put a sub-query that calculates the average of the quantity plus one standard deviation across the entire result set (note: you will not need a GROUP BY clause if you are calculating it across the entire result set).

db<>fiddle here

CodePudding user response：

An old-school way is to calculate the STDDEV in a subquery. Since it'll only be 1 result, you can cross join it without worry.

SELECT movie_category, movie_title, movie_qty
-- , stddev_movie_qty
FROM movie
JOIN movie_types
  ON movie_types.movie_cat_id = movie.movie_cat_id
CROSS JOIN (
   SELECT 
     STDDEV(movie_qty) AS stddev_movie_qty 
   FROM movie
) stats
WHERE movie_qty > stddev_movie_qty

Demo on db<>fiddle here