Doug Fir Doug Fir - 7 months ago 9
SQL Question

Get the count() where created_date is cumulative and date based

I'm aware that there are several answers on SO about cumulative totals. I have experimented and have not found a solution to my problem.

Here is a sqlfiddle.

We have a contacts table with two fields, eid and create_time:

eid create_time
991772 April, 21 2016 11:34:21
989628 April, 17 2016 02:19:57
985557 April, 04 2016 09:56:39
981920 March, 30 2016 11:03:12
981111 March, 30 2016 09:36:48


I would like to select the number of new contacts in each month along with the size of our contacts database at the end of each month. New contacts by year and month is simple enough. For the size of the contacts table at the end of each month I did some research and found what looked to be a straight forwards method:

set @csum = 0;
select
year(c.create_time) as yr,
month(c.create_time) as mth,
count(c.eid) as new_contacts,
(@csum + count(c.eid)) as cumulative_contacts
from
contacts c
group by
yr,
mth


That runs but gives me unexpected results.

If I run:

select count(*) from contacts where date(create_time) < current_date


I get the total number of records in the table 146.

I therefore expected the final row in my query using @csum to have 146 for April 2016. It has only 3?

What my goal is for field cumulative_contacts:
For the record with e.g. January 2016.

select count(*) from contacts where date(create_time) < '2016-02-01';


And the record for February would have:

select count(*) from contacts where date(create_time) < '2016-03-01';


And so on

Answer

Try this, a bit of modification from your sql;)

CREATE TABLE IF NOT EXISTS `contacts` (
  `eid` char(50) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

INSERT INTO `contacts` (`eid`, `create_time`) VALUES
('991772', '2016-04-21 11:34:21'),
('989628', '2016-04-17 02:19:57'),
('985557', '2016-04-04 09:56:39'),
('981920', '2016-03-30 11:03:12'),
('981111', '2016-03-30 09:36:48');

SET @csum = 0;
SELECT t.*, @csum:=(@csum + new_contacts) AS cumulative_contacts
FROM (
    SELECT YEAR(c.create_time) AS yr, MONTH(c.create_time) AS mth, COUNT(c.eid) AS new_contacts
    FROM contacts c
    GROUP BY yr, mth) t

Output results is

| yr   | mth | new_contacts | cumulative_contacts | 
 ------ ----- -------------- ---------------------
| 2016 | 3   | 2            | 2                   | 
| 2016 | 4   | 3            | 5                   | 
Comments