Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle/SQL: Include zero counts when joining non-existent table/data

Posted on 2014-01-22
7
Medium Priority
?
1,495 Views
Last Modified: 2014-01-22
Hi Experts,

I'm trying to construct a query that includes zero counts for values that don't exists in another table:

My Table:
name  |total1|total2|date
george|10      |20     |09-JAN-14
jim      |15      |5       |09-JAN-14
john    |8        |25     |09-JAN-14
george|30      |20     |10-JAN-14
jim      |15      |25     |10-JAN-14
john    |18      |22     |10-JAN-14
george|30      |20     |11-JAN-14
jim      |15      |25     |11-JAN-14
john    |18      |22     |11-JAN-14

Query:
select *
from myTable
where date in ('09-JAN-14','10-JAN-14')
order by name

Results:
name  |total1|total2|date
george|10      |20     |09-JAN-14
george|30      |20     |10-JAN-14
jim      |15      |5       |09-JAN-14
jim      |15      |25     |10-JAN-14
john    |8        |25     |09-JAN-14
john    |18      |22     |10-JAN-14

If I include a date that doesn't exist in the data e.g.:

select *
from myTable
where date in ('15-JAN-14')
order by name

I get no results. That's not what I want. I want to display zero counts e.g.

name  |total1|total2|date
george|0       |0        |15-JAN-14
jim      |0      |0        |15-JAN-14
john    |0      |0        |15-JAN-14

I'll appreciate any help.

Thanks in advance...
0
Comment
Question by:rmartes
  • 5
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39799976
That's a more complicated  query than you might think.

Try this...


  SELECT n.name,
         NVL(t.total1, 0) total1,
         NVL(t.total2, 0) total2,
         d.date
    FROM mytable t
         RIGHT JOIN (SELECT DISTINCT name FROM mytable) n ON t.name = n.name
         RIGHT JOIN (SELECT '09-JAN-14' date FROM DUAL
                     UNION ALL
                     SELECT '10-JAN-14' FROM DUAL) d
             ON t.date = d.date
ORDER BY name
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39799995
hmmm, no that doesn't quite work for the missing data either but I think I misunderstood the request anyway - stay tuned, I'll try again.  :)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39800016
what if you had this...

where date in ('15-JAN-14', '16-JAN-14')


would you want 6 rows of 0 values?

or what about '10-JAN-14, '15-JAN-14'

do you want both the populated and unpopulated values?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:rmartes
ID: 39800311
@sdstuber

Exactly....

where date in ('15-JAN-14', '16-JAN-14') should show 6 rows of zero values

and date in ('10-JAN-14, '15-JAN-14') should be combination of both
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39800345
try this...  you'll have to put your list of dates into a subquery so it can be used as a join, not just as an IN-List

With d
     AS (SELECT '09-JAN-14' mydate FROM DUAL
         UNION ALL
         SELECT '10-JAN-14' FROM DUAL
        )
SELECT t.*
  FROM mytable t, d
 WHERE t.mydate = d.mydate
UNION ALL
SELECT DISTINCT name,
                0,
                0,
                mydate
  FROM (SELECT DISTINCT name FROM mytable) t CROSS JOIN d
 WHERE (t.name, d.mydate) NOT IN (SELECT name, mydate FROM mytable)
ORDER BY 1;

Open in new window



or for not existing data...


With d
     AS (SELECT '15-JAN-14' mydate FROM DUAL
         UNION ALL
         SELECT '16-JAN-14' FROM DUAL)
SELECT t.*
  FROM mytable t, d
 WHERE t.mydate = d.mydate
UNION ALL
SELECT DISTINCT name,
                0,
                0,
                mydate
  FROM (SELECT DISTINCT name FROM mytable) t CROSS JOIN d
 WHERE (t.name, d.mydate) NOT IN (SELECT name, mydate FROM mytable)
ORDER BY 1;

Open in new window



or for a mix



With d
     AS (SELECT '09-JAN-14' mydate FROM DUAL
         UNION ALL
         SELECT '10-JAN-14' FROM DUAL
         UNION ALL
         SELECT '15-JAN-14' FROM DUAL
         UNION ALL
         SELECT '16-JAN-14' FROM DUAL)
SELECT t.*
  FROM mytable t, d
 WHERE t.mydate = d.mydate
UNION ALL
SELECT DISTINCT name,
                0,
                0,
                mydate
  FROM (SELECT DISTINCT name FROM mytable) t CROSS JOIN d
 WHERE (t.name, d.mydate) NOT IN (SELECT name, mydate FROM mytable)
ORDER BY 1;

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39800355
As a simpler syntax for your parameters, try something like this for the "d" portion.
The rest of the query would remain the same


With d
     AS (SELECT COLUMN_VALUE mydate
           FROM TABLE(
                    sys.ora_mining_varchar2_nt(
                        '09-JAN-14',
                        '10-JAN-14',
                        '15-JAN-14',
                        '16-JAN-14'
                    )
                ))

of course, that returns the values a strings, if you want dates
you might want to do something like this...


with d
     AS (SELECT to_date(COLUMN_VALUE,'dd-MON-RR') mydate
           FROM TABLE(
                    sys.ora_mining_varchar2_nt(
                        '09-JAN-14',
                        '10-JAN-14',
                        '15-JAN-14',
                        '16-JAN-14'
                    )
                ))
0
 

Author Closing Comment

by:rmartes
ID: 39801374
Funny thing, I was trying to do it with a WITH clause myself, but couldn't quite get it. Excellent! Thank you so much!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question