Mysql if not exists return 0

Question

I have a mysql table like this :

date           data
01-01-2005       10 |
02-01-2005       11|
03-01-2005       13|
07-01-2005       20|

How i can list like below

date           data:
01-01-2005       10|
02-01-2005       11|
03-01-2005       13|
04-01-2005       0|
05-01-2005       0|
06-01-2005       0|
07-01-2005       20|

EDIT: i use a calendar table like this:

date:
01-01-2005 for all 365 days.

this my best opinion but it is not working :

SELECT c.steps, IFNULL(c.steps, 0) value, r.calendar_date
  FROM log_activities c
  LEFT JOIN calendar r
    ON (r.calendar_date = c.date_logged)
 WHERE email = 'a@mail.com'
 ORDER BY c.date_logged DESC

Search for calendar table.Also add a query,no matter how wrong.Show some effort. — Mihai, Mar 11 '14 at 20:42
http://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query — Mihai, Mar 11 '14 at 20:46
I would try what @Mihai linked and I would also think about how you are adding data to your table. You should think about seeding every day even if it is to have a 0 amount of data. — the_pete, Mar 11 '14 at 20:49

score 0 · Accepted Answer · answered Mar 11 '14 at 20:52

0

Try:

SELECT c.steps, IFNULL(c.steps, 0) value, r.calendar_date
  FROM log_activities c
 right JOIN calendar r
    ON r.calendar_date = c.date_logged
 WHERE email = 'a@mail.com'
   and substr(r.calendar_date, 4, 2) = '01'
   and r.calendar_date between
       (select min(date_logged) from log_activities) and
       (select max(date_logged) from log_activities)
 ORDER BY c.date_logged DESC

answered Mar 11 '14 at 20:52

Brian DeMilia

13,103
1
23
33

Can you give example data? Are you sure that the 1st of each month is indeed on the calendar table? (the months you want to show up, but which don't have records on the log). Also I don't know if your criteria on that email address was an example ('a@mail.com') but you would get rid of that if it is. – Brian DeMilia Mar 11 '14 at 21:09
yes i change email address and i delete substr then i got same result . But this result still is missing.(not add any 0's date) – erdinc Mar 11 '14 at 21:23
I'd have to see the calendar table. The query should show all dates in the calendar table (only those on the first of the month) and if the same date is on the log_activities table, it should show the applicable data. The fact that no rows come back suggests that the calendar table does not contain dates in the range. – Brian DeMilia Mar 11 '14 at 21:36
yes i have some rows in this range. my calendar table have only one row and like this: 01-01-2005 02-01-2005 03-01-2005 .. ... 27-12-2006 – erdinc Mar 11 '14 at 21:56
@erdinc I just loaded some sample data to test the query here: http://sqlfiddle.com/#!2/f3766/2/0 and it seems to work as expected, I would double check the source data. – Brian DeMilia Mar 11 '14 at 22:07

Mysql if not exists return 0

1 Answers1