CODEWITHSUNDEEP
mysqlsqlxampp
Petahwil
Petahwil

Reputation: 437

Find the genre with longest average movie length. SQL

I am new to SQL and i was trying to get this problem solved on mySQL. which is finding the genre with longest average movie length. I have got the the genre & movies lengths lined up below but I am really confused on how to go about taking the average of each genre. Genre:

taking the avg of there individual genres and displaying the max avg of them. Any help would be greatly appreciated thanks

SELECT tblGenre.Genre, tblMovie.Length
FROM tblMovie, tblMovieGenre, tblGenre
WHERE (((tblMovie.movie_ID)=[tblMovieGenre].[Movie]) AND ((tblMovieGenre.Genre)=[tblGenre].[genre_ID]))
GROUP BY tblGenre.Genre, tblMovie.Length;

OutPut

Genre   Length
Action  81 
Action  97 
Action  110
Action  130
Action  136
Action  145
Action  152
Action  165
Adventure   90
Adventure   109
Adventure   111
Adventure   132
Adventure   145
Adventure   178
Adventure   179
Adventure   201
Comedy  87
Comedy  90
Comedy  97
Comedy  109
Comedy  111
Drama   80
Drama   81
Drama   90
Drama   91
Drama   93
Drama   95
Drama   102
Drama   109
Drama   111
Drama   130
Drama   132
Drama   139
Drama   151
Drama   152
Drama   161
Drama   175
Drama   194
Sci-Fi  110
Sci-Fi  136
Sci-Fi  145
Thriller    84
Thriller    91
Thriller    93
Thriller    99
Thriller    110
Thriller    151
Thriller    165

Upvotes: 0

Views: 4048

Answers (5)

user19345959
user19345959

Reputation: 1

select genre, round(avg(duration)) as avr_duration_of_movie from genre as g inner join movie as m on g.movie_id = m.id group by genre order by avr_duration_of_movie desc;

Upvotes: 0

Petahwil
Petahwil

Reputation: 437

 SELECT TOP 1 tblGenre.Genre, Avg(tblMovie.Length) AS AVG_Moive
 FROM tblMovie, tblMovieGenre, tblGenre
 WHERE (((tblMovie.movie_ID)=[tblMovieGenre].[Movie]) AND ((tblMovieGenre.Genre)=[tblGenre].[genre_ID]))
 GROUP BY tblGenre.Genre
 ORDER BY Max(tblMovie.Length) DESC;

Upvotes: 0

viduka
viduka

Reputation: 153

Maybe what do you want something like this. to get average movie length you can use AVG(Column).

SELECT tblGenre.Genre, AVG(tblMovie.Length)
FROM tblMovie, tblMovieGenre, tblGenre
WHERE tblMovie.movie_ID = tblMovieGenre.Movie 
AND tblMovieGenre.Genre = tblGenre.genre_ID
GROUP BY tblGenre.Genre;

to get the longest movie length from average movie length

SELECT tblGenre.Genre, tblMovie.Length
FROM tblMovie, tblMovieGenre, tblGenre
    WHERE tblMovie.movie_ID = tblMovieGenre.Movie 
    AND tblMovieGenre.Genre = tblGenre.genre_ID
    AND tblMovie.Length > (SELECT tblGenre.Genre, AVG(tblMovie.Length)
              FROM tblMovie, tblMovieGenre, tblGenre
              WHERE tblMovie.movie_ID = tblMovieGenre.Movie 
              AND tblMovieGenre.Genre = tblGenre.genre_ID
              GROUP BY tblGenre.Genre);

Upvotes: 2

somaredi
somaredi

Reputation: 9

I think this is what you are looking for:

SELECT tblGenre.Genre, AVERAGE(tblMovie.Length)
FROM tblMovie, tblMovieGenre, tblGenre
WHERE (((tblMovie.movie_ID)=[tblMovieGenre].[Movie]) AND ((tblMovieGenre.Genre)=[tblGenre].[genre_ID]))
GROUP BY tblGenre.Genre;

Upvotes: 1

simplycoding
simplycoding

Reputation: 2967

I don't know MySQL too well, but if I understand correctly, this is what you want:

SELECT tblGenre.Genre, AVERAGE(tblMovie.Length)
FROM tblMovie, tblMovieGenre, tblGenre
WHERE (((tblMovie.movie_ID)=[tblMovieGenre].[Movie]) AND ((tblMovieGenre.Genre)=[tblGenre].[genre_ID]))
GROUP BY tblGenre.Genre
ORDER BY 2 desc
limit 1;

Upvotes: 2

Related Questions