Display counts for missing dates in mysql query

Question

After I run the following query:

SELECT 
    DATE_FORMAT( added_datetime, '%Y-%m-%d' ) AS date, 
    SUM( gender = 'male' ) AS male, 
    SUM( gender = 'female' ) AS female
FROM social_user 
WHERE social_network = 'FBuser' 
AND date( added_datetime ) BETWEEN date('2014-11-18') AND date('2014-11-20')
GROUP BY date( added_datetime )

I got this result:

date        | male | female
------------+------+--------
2014-11-19  |    2 |      0

But I need something different like this screenshot:

date        | male | female
------------+------+--------
2014-11-18  |    0 |      0
2014-11-19  |    1 |      0
2014-11-20  |    2 |      0
2014-11-21  |    0 |      0
...    
...

I need all date in my result set which is I used in my between operator.

Sorry for my poor English and I don't have enough reputation for attached image.

Answer 1

The dates that do not exist in your data cannot magically appear in the results. One solution is to create a table of dates that contains all the dates inside a very long timespan:

CREATE TABLE datelist (DATE DATETIME NOT NULL PRIMARY KEY);
-- dates in the past
INSERT INTO datelist VALUES ('2014-11-18');
INSERT INTO datelist VALUES ('2014-11-19');
INSERT INTO datelist VALUES ('2014-11-20');
INSERT INTO datelist VALUES ('2014-11-21');
-- dates in the future

And use it in your JOIN query:

SELECT
    datelist.date,
    SUM(gender = 'male') AS male,
    SUM(gender = 'female') AS female
FROM datelist
LEFT JOIN social_user ON datelist.date = DATE(social_user.added_datetime)
WHERE datelist.date BETWEEN '2014-11-18' AND '2014-11-20' AND (
    social_user.id /* or whatever primary key */ IS NULL OR social_network = 'FBuser'
)
GROUP BY datelist.date

Sample output:

date                 male  female
-------------------  ----  ------
2014-11-18 00:00:00  NULL  NULL
2014-11-19 00:00:00  2     0
2014-11-20 00:00:00  1     2

In the above example, the two NULL columns indicates that there is no matching social_user record for that day. The WHERE clause is tweaked to include such rows.