This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 3606
    
  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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    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?
 
Similar Threads
How to store arraylist in xml file
Get the day of the week & month in word from constructor into main with input
Java Calendar Dates
Java Calendar Dates
Problem with carrying over the correct months while using GetDate()