Display 0 in count query using UNION and a subquery, Join not allowed

user_3367856 0
Viewed 82

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.

oracle sql
4 Answers

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

There are plenty of ways we could do this query, but I specifically chose this way because it builds on what you already did and know, and outlines how we can group and summarise data on a sub-level and then connect it to more data at an outer level

It's additionally useful because you might want to add some extra data in eg from the sales table to know which movies being watched are the highest earning ones. If you choose salman's route (which is right in this context) you'll encounter problems with the stats as you add more tables because row numbers will multiply more than you expect. By grouping eg the sales and the watching in subqueries you can join them to the main table without causing duplication of rows (also called a Cartesian product). As a result for queries of this nature I tend to suggest grouping and aggregating in subqueries before joining to other tables to preserve a 1:1 relationship between the main table (movies) and the outputs of subqueries (eg watching, sales, count of actors, etc) that contain data related to the main movie but not necessarily related to each other

user_14106640

try to do this

select movie.title, count(watch.persID) 
from movies left outer join watch on movies.id = watch.movieID 
group by movieID;

I think it could work

user_80879660

Since you're insisting on using UNION and sub-query, here is an answer that uses both:

SELECT movieID, COUNT(*) AS watchCount
FROM watch
GROUP BY movieID
UNION
SELECT movieID, 0
FROM movies
WHERE movieID NOT IN (
    SELECT movieID
    FROM watch
    WHERE movieID IS NOT NULL
)
user_870150

Using UNION only:

SELECT movieID, COUNT(*) -1 AS watchCount -- subtract the movie row
FROM
 ( 
    SELECT movieID -- multiple rows per watched movie
    FROM watch
    UNION ALL
    SELECT movieID -- exactly one row per movie
    FROM movies
 )

or (might be faster if there are lots of rows in watch)

SELECT movieID, max(cnt)
FROM
 ( 
    SELECT movieID, count(*) as cnt
    FROM watch
    GROUP BY movieID
    UNION ALL
    SELECT movieID, 0
    FROM movies
 )
GROUP BY movieID
user_25279050
Related