0

I have this activity where I query data from the previous month to get the total count of transactions per day in that month. The sample result I'm getting is this:

--------------------
TIMESTAMP | COUNT  
----------|---------
7/3/2014  | 1
7/21/2014 | 1
7/22/2014 | 8
7/23/2014 | 1
7/24/2014 | 3
7/25/2014 | 3
7/26/2014 | 4
7/28/2014 | 4
7/30/2014 | 3
7/31/2014 | 3

In my case, I get that result since there are days where there are no transactions. Is there a way to get a result from a query where all the dates from the previous month are present like this?

--------------------
TIMESTAMP | COUNT  
----------|---------
7/1/2014  | 0
7/2/2014  | 0
7/3/2014  | 1
7/4/2014  | 0
7/5/2014  | 0
7/6/2014  | 0
7/7/2014  | 0
7/8/2014  | 0
7/9/2014  | 0
7/10/2014 | 0
7/11/2014 | 0
7/12/2014 | 0
7/13/2014 | 0
7/14/2014 | 0
7/15/2014 | 0
7/16/2014 | 0
7/17/2014 | 0
7/18/2014 | 0
7/19/2014 | 0
7/20/2014 | 0
7/21/2014 | 1
7/22/2014 | 8
7/23/2014 | 1
7/24/2014 | 3
7/25/2014 | 3
7/26/2014 | 4
7/27/2014 | 0
7/28/2014 | 4
7/29/2014 | 0
7/30/2014 | 3
7/31/2014 | 3

This would really help me a lot in my reporting if it's possible. Thanks in advance to those who'll be able to help me. :D

user3599448
  • 1
  • 2
    Oracle or mysql? Can't be both – Arion Aug 04 '14 at 10:55
  • If it's Oracle then have a look at http://stackoverflow.com/questions/4644562/oracle-sql-query-to-list-all-the-dates-of-previous-month how to generate a list of dates for a whole month. You've got to left join this list with your existing query. – VMai Aug 04 '14 at 11:06
  • Hi, I'm using oracle. I tried that. I did a right join (The table on the right contains all dates of that month), but still have no luck. Can you give me an example query so that I can see where I made a mistake? – user3599448 Aug 04 '14 at 13:24

0 Answers0