Display 0 in count query using UNION and a subquery

3495 views sql
3

I have 2 tables (movies and watch).

enter image description here

I want to know which movies is being watched by how many people.

I did this:

select movieID, count(persID) from watch group by persID;

which gives me basically what i want. The only problem is that movies that aren't being watched by anyone won't show up in my result table as 0 viewers but they are just left out.

I want to achieve this in two different ways. Using UNION and the other way using a subquery.

answered question

Hint: JOIN, GROUP BY.

4 Answers

9

You can try below

select movies.movieID,movies.title, count(persID) as noofwatch
from watch left join movies on watch.movieid=movies.movieid
group by movies.movieID,movies.title

posted this
0

So you got the list of movies that are being watched and the counter - left join it into the list of all movies:

SELECT * FROM
  Movies m
  LEFT JOIN
  (select movieID, count(persID) as countwatch from watch group by persID) w
  ON m.movieid = w.movieid

Left join means you get all the movies, linked up with counts of only those being watched. If a movie is not being watched it's countwatch column will be null

If you want to turn that null into a 0, use COALESCE:

SELECT m.*, COALESCE(w.countwatch, 0) as countwatch FROM
  Movies m
  LEFT JOIN
  (select movieID, count(persID) as countwatch from watch group by persID) w
  ON m.movieid = w.movieid

posted this
8

You need an outer join:

select movies.movieID, count(watch.movieID) 
from movies 
LEFT join watch on movies.movieID = watch.movieID
group by movies.movieID;

posted this
11

try to do this

select movieID, count(persID) from watch group by movieID;

I think could work

posted this

Have an answer?

JD

Please login first before posting an answer.