• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to show results as zero if no record exists?

 
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
There is no greater crime than stealing somebody's best friend. I miss you tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic