CODEWITHSUNDEEP
mysqlsql
Mohammad Zahrawy
Mohammad Zahrawy

Reputation: 305

selecting count, percentage and compare in mysql

I have a MYSQL database that has two tables.

The first one called movies_interaction with the following parameters: movie_id, watcher_id, movie_duration. the second table is movies_additional with the following parameters: movie_id and movie_length

What I need is to connect these two tables by which I need to retrieve the movies which was watched more than once AND more than 60% of its duration for each user.

This is so far what I wrote, but I know its wrong, so don't take that on me please.

SELECT watcher_id, COUNT(*)
AS video_count FROM movie_interaction
GROUP BY movie_id HAVING COUNT(*) >= 2 AND
movies_interaction.movie_duration *100 / movies_additional.movie_length >= 60

the fourth line of the code is where I need help!

Result can look like this: watcher_id1 = 9, watcher_id2 = 13...etc.

Thank you very much.

Upvotes: 0

Views: 46

Answers (1)

Alex Dupuis
Alex Dupuis

Reputation: 366

I'm not exactly sure on you need but I think that way look like that. 

SELECT A.movie_id, COUNT(I.watcher_id) AS video_count 
FROM movie_interaction I
INNER JOIN movie_additional A ON A.movie_id = I.movie_id AND
                                 I.movie_duration / A.movie_length >= 0.60
GROUP BY A.movie_id
HAVING COUNT(I.watcher_id) >= 2

This will give you a list of movies watched more than one time for more that 60>%

Upvotes: 1

Related Questions