Solved

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

Posted on 2014-01-22
7
1,396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 500 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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

630 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