Solved

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

Posted on 2014-01-22
7
1,363 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

751 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