Skip to content

Instantly share code, notes, and snippets.

@klanjabrik

klanjabrik/Readme.MD

Last active Aug 29, 2015
Embed
What would you like to do?
MySQL: Return "0" even when data doesn't exist

What is happening:

DATE COUNTER
2012-01-01 32
2012-01-02 28
2012-01-04 12
2012-01-05 23

As you can see, on 2012-01-03 dates there isn't any data. So the goal is to create a query (without procedure) to produce below result:

DATE COUNTER
2012-01-01 32
2012-01-02 28
2012-01-03 1
2012-01-04 12
2012-01-05 23
SELECT
a.join_date, IFNULL(b.total, 0) theCount
FROM
(SELECT @curDate := Date_Add(@curDate, interval 1 day) AS join_date
FROM (SELECT @curDate := Date_Add('2012-01-01', interval -1 day) ) sqlvars, users LIMIT 5
)
a
LEFT JOIN (
SELECT DATE_FORMAT(FROM_UNIXTIME(created_on), '%Y-%m-%d') AS tanggal, count(*) AS total
FROM users u
WHERE u.active = 1
GROUP BY tanggal
) b on b.tanggal = a.join_date
ORDER BY a.join_date ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.