Getting daily counts for events that don't happen every day

Viewed 406

I have a customer table in which a new row is inserted when a customer signup occurs.

Problem

I want to know the total number of signup per day for a given date range.

For example, find the total number of signup each day from 2015-07-01 to 2015-07-10

customer table sample data [relevant columns shown]

customerid    username    created
1             mrbean      2015-06-01
2             tom         2015-07-01
3             jerry       2015-07-01
4             bond        2015-07-02
5             superman    2015-07-10
6             tintin      2015-08-01
7             batman      2015-08-01
8             joker       2015-08-01

Required Output

created      signup
2015-07-01   2
2015-07-02   1
2015-07-03   0
2015-07-04   0
2015-07-05   0
2015-07-06   0
2015-07-07   0
2015-07-08   0
2015-07-09   0
2015-07-10   1

Query used

SELECT 
    DATE(created) AS created, COUNT(1) AS signup
FROM
    customer
WHERE
    DATE(created) BETWEEN '2015-07-01' AND '2015-07-10'
GROUP BY DATE(created)
ORDER BY DATE(created)

I am getting the following output:

created      signup
2015-07-01   2
2015-07-02   1
2015-07-10   1

What modification should I make in the query to get the required output?

3 Answers
Related