CODEWITHSUNDEEP
mysql
Sean
Sean

Reputation: 12433

Count of rows grouped by date and older

I have a table that contains records for registration for an event.

table data

I am able to get a count of registrations, grouped by the date -

SELECT DATE(`date_registered`) as `date`, COUNT(*) as `total`  
FROM `registration`
GROUP BY DATE(`date_registered`)

count by date

But I would like to get the running count, based on the date -
count by date all

So I am looking at doing something like -

SELECT DATE(`date_registered`) as `date`, COUNT(*) as `total`  
FROM `registration`
GROUP BY DATE(`date_registered`) <= DATE(`date_registered`)

I attempted to do a LEFT JOIN with a count of all the dates prior, but that does not work - 

SELECT DATE(r1.`date_registered`) as `date`, COUNT(*)+r3.preTotal as `total`
FROM `registration` r1
LEFT JOIN (
  SELECT COUNT(*) as preTotal
  FROM `registration` r2
  WHERE DATE(r2.`date_registered`) < DATE(r1.`date_registered`)
  ) r3
GROUP BY DATE(r1.`date_registered`)

I have created a SQLFiddle
with the basic count - http://sqlfiddle.com/#!9/420d1d/5
and with my failed attempt - http://sqlfiddle.com/#!9/420d1d/6

I assume there is a simple way that I am missing.

Edit Here are the same at rextester.com
basic count - http://rextester.com/GISU91151
and failed attempt - http://rextester.com/RDTFK34261

Upvotes: 0

Views: 38

Answers (2)

Gerry
Gerry

Reputation: 10507

How about this:

SELECT
  DATE(`date_registered`) AS date,
  (
    SELECT COUNT(*)
    FROM  `registration` t2
    WHERE DATE(t2.`date_registered`) <= DATE(t1.`date_registered`)
  ) AS total
FROM  `registration` t1
GROUP BY DATE(`date_registered`);

Upvotes: 2

Raymond Nijland
Raymond Nijland

Reputation: 11602

One way to make this work is creating a corelated subquery.

Query

SELECT
     registration_counted.date
  , (registration_counted.total + registration_counted.preCount) AS total  
FROM ( 

  SELECT
     DATE(date_registered) AS DATE 
   , COUNT(*) AS total
   , ( 
     SELECT 
       COUNT(*) 
     FROM 
       registration AS registration2
     WHERE
       DATE(registration2.date_registered) < DATE(registration1.date_registered) 
     )
    AS
      preCount
   FROM 
      registration AS registration1
   GROUP BY 
     DATE(date_registered)

  ORDER BY 
    DATE(date_registered) ASC

)
  AS 
    registration_counted

Result

date         total  
----------  --------
2014-01-16         1
2014-01-20         2
2014-01-22         3
2014-01-31        18
2014-02-01        19
2014-02-04        22
2014-02-12        23
2014-02-19        24
2014-02-20        28
2014-02-26        30
2014-02-27        34
2014-02-28        37

Upvotes: 2

Related Questions