aspose file tools*
The moose likes JDBC and the fly likes How to show results as zero if no record exists? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to show results as zero if no record exists?" Watch "How to show results as zero if no record exists?" New topic
Author

How to show results as zero if no record exists?

abu alfouz
Ranch Hand

Joined: Nov 20, 2006
Posts: 44
Hello,

I have a problem. For every day of a month (lets say February) I want to show records from a PhoneCall table stored in a MySQL database.
The PhoneCall table looks like this:

Record No. Date PhoneNumber
------------- --------------------- -----------------
1 2010-02-01 10:04 491111111
2 2010-02-03 08:35 492222222
3 2010-02-03 08:40 491234567

You see for the 2nd February NO phone calls are in the table.
Now I want to create a query that shows the phone calls for EVERY day of February 2005:

Date No Of Calls
--------- ----------------
2005-02-01 1
2005-02-02 0
2005-02-03 2

But if I do a query like:

select Date, count(Date) As No Of Calls
from PhoneCalls
where Date >= '2005-02-01' and Date <= '2005-02-28'
group by Date

I don't get a record/result for the 2nd February.

So I wanted to use a LEFT JOIN, but how to create the Date-table that holds all days of current month?


So how can I solve this problem?

Any ideas are appreciated?

SCJP 1.5
SCWCD 1.4
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Most databases provide some means to generate rows artificially. I don't know MySQL, but a quick search for mysql generate rows yields this:

http://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql

You'll then use date arithmetics to convert generated integers into dates and then join to it, as you mentioned.

However, if your goal is just to display the data in your application, I'd suggest to consider creating the missing dates in the client code.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

abu alfouz wrote:So I wanted to use a LEFT JOIN, but how to create the Date-table that holds all days of current month?


You could create the date table. Alternatively, this seems like a display issue. As Martin pointed out, you could resolve this on the presentation layer of your application, not the data layer.


My Blog: Down Home Country Coding with Scott Selikoff
Ed Ward
Ranch Hand

Joined: Jan 30, 2006
Posts: 147
Not really a JDBC comment, but several of the systems I've worked on actually had a CALENDAR dimension table defined which broke down a given date into various formats, representations and attributes. It was then quite easy to join a fact table on the dimension table via some key field (date_key). In this way we quite commonly (and very easily) would create "faux" records via SQL if there was no data for a given date in the fact table.

I should note, these were ETL and reporting data warehouse systems so whether or not you do something like that is really more requirement/system specific. For these, more often than not the final output of most processes and external entities accessing the data needed to know whether or not there was data for a given day and what those values were, even if zero. This approach centralized the issue so that simliar logic was not needed in various external, unrelated systems.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to show results as zero if no record exists?