Databases
StackOverflow2013
Postsdbo
POGetting count to return 0 in Oracle SQL using a union
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGetting count to return 0 in Oracle SQL using a union
    primarykey
    data
    text
    <p>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. </p> <p>Here's my rather large query (it has to join many tables together):</p> <pre><code>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; </code></pre> <p>However, when I run the query I get:</p> <pre><code>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. </code></pre> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USuser2793442
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload