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

Question

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?

I think you just need to change your query code to COUNT(created) As SignUp — Davy C, Mar 19 '16 at 20:28
Possible duplicate of http://stackoverflow.com/q/2157282/1920758 — Nick, Mar 19 '16 at 20:42

score 2 · Accepted Answer · answered Mar 19 '16 at 20:44

You're looking for a way to get all the days listed, even those days that aren't represented in your customer table. This is a notorious pain in the neck in SQL. That's because in its pure form SQL lacks the concept of a contiguous sequence of anything ... cardinal numbers, days, whatever.

So, you need to introduce a table containing a source of contiguous cardinal numbers, or dates, or something, and then LEFT JOIN your existing data to that table.

There are a few ways of doing that. One is to create yourself a calendar table with a row for every day in the present decade or century or whatever, then join to it. (That table won't be very big compared to the capability of a modern database.

Let's say you have that table, and it has a column named date. Then you'd do this.

 SELECT calendar.date AS created,
        ISNULL(a.customer_count, 0) AS customer_count
   FROM calendar
   LEFT JOIN ( 
            SELECT COUNT(*) AS customer_count,
                   DATE(created) AS created
              FROM customer
             GROUP BY DATE(created)
        ) a ON calendar.date = a.created
   WHERE calendar.date BETWEEN start AND finish 
   ORDER BY calendar.date

Notice a couple of things. First, the LEFT JOIN from the calendar table to your data set. If you use an ordinary JOIN the missing data in your data set will suppress the rows from the calendar.

Second, the ISNULL in the toplevel SELECT to turn the missing, null, values from your dataset into zero values.

Now, you ask, where can I get that calendar table? I respectfully suggest you look that up, and ask another question if you can't figure it out.

I wrote a little essay on this, which you can find here.http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/

score 1 · Answer 2 · answered Mar 19 '16 at 20:39

1

Look here

Create teble with calendar and join it in your query.

answered Mar 19 '16 at 20:39

Nick

9,735
7
59
89

Davy C · Answer 3 · 2016-03-19T21:04:18.053

DECLARE @MinDate DATE = '2015-07-01',
        @MaxDate DATE = '2015-07-10';

Create Table tblTempDates 
(created date, signup int)

insert into tblTempDates
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate),  0 As Signup
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

Create Table tblTempQueryDates
(created date, signup int)

INSERT INTO tblTempQueryDates
SELECT 
    created AS created, COUNT(scandate) AS signup
FROM
    customer
WHERE
    created BETWEEN @MinDate AND @MaxDate
GROUP BY created

UPDATE    tblTempDates
SET  tblTempDates.signup = tblTempQueryDates.signup            
FROM         tblTempDates INNER JOIN
                      tblTempQueryDates ON tblTempDates.created = tblTempQueryDates.created

select * from tblTempDates
order by created

Drop Table tblTempDates
Drop Table tblTempQueryDates

Not pretty, but it gives you what you want.

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

3 Answers3

Linked

Related