CODEWITHSUNDEEP
sqloracle-databasecount
user2793442
user2793442

Reputation: 129

Getting count to return 0 in Oracle SQL using a union

I am attempting to write a query that shows for all videos how many times each has been rented. Even if it has been rented no times, the count should show 0. I know I am supposed to use a union to get these results, but trying to get count to return a 0 has been unsuccessful.

Here's my rather large query (it has to join many tables together):

select r.vid_id, movie_title, vid_format, count(r.client_num) "Num_Rental"
from rental r, client c, movie m, video v
where r.client_num = c.client_num
        and r.vid_id = v.vid_id
                and m.movie_num = v.movie_num
        group by r.vid_id, movie_title, vid_format
union
select r.vid_id, movie_title, vid_format, count(*)
from rental r, client c, movie m, video v
where r.client_num = c.client_num
        and r.vid_id = v.vid_id
                and m.movie_num = v.movie_num
        group by r.vid_id, movie_title, vid_format
order by movie_title, vid_format;

However, when I run the query I get:

VID_ID MOVIE_TITLE                              VID_FOR Num_Rental              
------ ---------------------------------------- ------- ----------              
120011 Gone with the Wind                       Blu-Ray          4              
130012 Gone with the Wind                       DVD              3              
220013 Indiana Jones and the Temple of Doom     Blu-Ray          1              
230012 Indiana Jones and the Temple of Doom     DVD              1              
210011 Indiana Jones and the Temple of Doom     HD-DVD           2              
130022 It's a Wonderful Life                    DVD              1              
420011 One Flew Over the Cuckoo's Nest          Blu-Ray          1              
230033 Star Wars                                DVD              1              
210031 Star Wars                                HD-DVD           2              
210041 The Empire Strikes Back                  HD-DVD           1              
130031 The Muppet Movie                         DVD              3              

VID_ID MOVIE_TITLE                              VID_FOR Num_Rental              
------ ---------------------------------------- ------- ----------              
220062 The Phantom Menace                       Blu-Ray          1              
330023 Toy Story                                DVD              1              
320032 Toy Story 2                              Blu-Ray          1              

14 rows selected.

In the union, somehow the count should return 0 for Num_rental for many of the videos, but I'm not sure what approach to take. If anyone could point me to some documentation or something like that it would be much appreciated.

Upvotes: 1

Views: 293

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

It is a bit difficult to find the answer without knowing exactly your current schema nor having sample data for it. But this should get you the results you're looking for or at least point you in the right direction:

select r.vid_id, movie_title, vid_format, count(r.client_num) "Num_Rental"
from video v
left join rental r on r.vid_id = v.vid_id
left join movie m on m.movie_num = v.movie_num
left join client c on r.client_num = c.client_num
group by r.vid_id, movie_title, vid_format

Anyway, I wonder why you need to join on client and movie in order to count rentals of videos... it seems you can join rental and video directly. making the rest of the joins unnecessary.

Upvotes: 1

Related Questions