This week's book giveaway is in the Java in General forum.
We're giving away four copies of Think Java: How to Think Like a Computer Scientist and have Allen B. Downey & Chris Mayfield on-line!
See this thread for details.
Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to show results as zero if no record exists?

 
abu alfouz
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ed Ward
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic