0

I'm trying to produce a query to display a chart for the past week. The data should include the daily total cumulative registrations. So on day one if there are 5 registrations and day 2 there is one, day 2 should read 6.

I'd like to get data for the previous 7 days (my query uses dummy data, so the time calculation is temporary) and the query I have works perfectly, except if there is a day without registrations - it simply skips the day. What it should do is provide the running total (equal to the last days total).

Here's the table

CREATE TABLE registrations
(`id` int, `name` varchar(50), `created` int);

INSERT INTO registrations
(`id`, `name`, `created`)
VALUES
(1, 'Bob', 1371584524),
(2, 'George', 1371586524),
(3, 'Harold', 1371843424),
(4, 'Ted', 1371845724),
(5, 'Simon', 1373141331),
(6, 'Phil', 1373141724),
(7, 'Dave', 1373313524),
(8, 'Barry', 1373314524),
(9, 'Doug', 1373486524),
(10, 'Earl', 1373487324),
(11, 'Peter', 1373573134),
(12, 'Edward', 1373573725);

and here's my query so far

SELECT days.date_of_year, COUNT(r.id) AS total_reg
FROM (
     SELECT DISTINCT (FROM_UNIXTIME(ur.created, '%Y-%m-%d')) date_of_year
         FROM `registrations` ur
         WHERE ur.created > (UNIX_TIMESTAMP(NOW())-999999)
 ) days
JOIN `registrations` r ON days.date_of_year >= (FROM_UNIXTIME(r.created, '%Y-%m-%d'))
GROUP BY days.date_of_year

This produces

+--------------+-----------+
| date_of_year | total_reg |
+--------------+-----------+
| 2013-07-06   | 6         |
| 2013-07-08   | 8         |
| 2013-07-10   | 10        |
| 2013-07-11   | 12        |
+--------------+-----------+

And what I'd like is

+--------------+-----------+
| date_of_year | total_reg |
+--------------+-----------+
| 2013-07-06   | 6         |
| 2013-07-07   | 6         |
| 2013-07-08   | 8         |
| 2013-07-09   | 8         |
| 2013-07-10   | 10        |
| 2013-07-11   | 12        |
+--------------+-----------+

Is this possible, or should I just write a middleware script to handle the days with no registations?

DeweyOx
  • 719
  • 5
  • 14
  • It *is* possible in SQL, but only if you have a table that contains all the desired dates (a table of constant values can be supplied in a subquery using a bunch of `UNION` commands) which would then be outer-joined to your query. Better to do it in middleware. – eggyal Jul 11 '13 at 20:51
  • but it's sooooo close :( – DeweyOx Jul 11 '13 at 20:53
  • Related: http://stackoverflow.com/questions/12936133/get-total-views-per-day-including-leaving-0-views-for-a-day-that-has-no-record?rq=1, http://stackoverflow.com/questions/4977951/mysql-if-row-doesnt-exist-grab-default-value – atk Jul 11 '13 at 20:53
  • Well, what if no records exists in the time span, then no records are returned as apposed to the running total. In that case, what should be done? – DeweyOx Jul 11 '13 at 21:22
  • Why do you need the database layer to do this? Why can't your application keep track of the last `total_reg` seen and use that for all dates up to that of the next record? – eggyal Jul 12 '13 at 00:00
  • Because "registrations" was for example purposes only. The actual application tracks account usage and I'm trying to calculate customers account activity in real time. Basically, accounts can have buckets, with numerous files in each bucket. I'd like to build a visual daily representation for the previous week of how many buckets are on the system, and how many files. – DeweyOx Jul 12 '13 at 03:16

0 Answers0