Solved

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

Posted on 2014-01-22
7
1,239 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 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:rmartes
Comment Utility
@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 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now