CODEWITHSUNDEEP
mysqlsqldatetimegroup-by
Nick
Nick

Reputation: 2907

MySQL query for displaying data grouped by date that is incremented by a day

I have been trying to come up with a query to display data in a particular fashion and I have been struggling with it.

Suppose with these data

ID Amount Date
1  10     10/10/2014 13:45
1  20     10/10/2014 14:56
1  05     10/10/2014 22:45
1  10     11/10/2014 23:04
1  30     14/10/2014 03:00
1  15     14/10/2014 04:34
1  15     15/10/2014 13:34
2  10     10/10/2014 16:05

I want to call the query with a start date and and end date and to display the results with a daily row that contains the sum of the amount for that day for that ID. So result would be:

For Id=1, StartDate=10/10/2014 and EndDate=14/10/2014:

Date        Sum
10/10/2014  35
11/10/2014  10
12/10/2014   0
13/10/2014   0
14/10/2014  45

Now down to the things I have already tried, I created a script in jFiddle:

CREATE TABLE TableName
    (`id` int, `amount` int, `timestamp` datetime)
;

INSERT INTO TableName
    (`id`, `amount`, `timestamp`)
VALUES
    (1, 10, '2013-01-06 12:23:56'),
    (1, 15, '2013-01-06 02:23:41'),
    (1, 15, '2013-01-07 14:23:42'),
    (1, 0,  '2013-01-08 04:23:56'),
    (1, 5,  '2013-01-08 16:23:25'),
    (1, 20, '2013-01-08 12:23:57'),
    (1, 10, '2013-01-08 23:23:40'),
    (1, 0,  '2013-01-09 07:23:56'),
    (1, 5,  '2013-01-12 17:23:25'),
    (1, 20, '2013-01-13 22:23:57'),
    (1, 10, '2013-01-14 09:23:40'),
    (1, 10, '2013-01-14 19:23:23'),
    (1, 35, '2013-01-15 15:23:55'),
    (1, 40, '2013-01-15 21:29:38'),
    (2, 40, '2013-01-06 02:31:59');

And I used it as a playground using various methods I found all over the internet with not much luck.

Any idea how I can get the data in that structure?

Upvotes: 0

Views: 134

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You need to generate all the dates. This is a pain, but the following works for your data:

select d.dte, coalesce(sum(tn.amount), 0)
from ((select date(timestamp) as dte from tablename) union
      (select date(timestamp) + interval 1 day from tablename) union
      (select date(timestamp) - interval 1 day from tablename)
     ) d left outer join
     tablename tn
     on date(tn.timestamp) = d.dte
group by d.dte
order by 1;

EDIT:

If you want to limit these to the dates in the table, then add:

where d.dte between (select min(date(timestamp) from tablename) and
                    (select max(date(timestamp) from tablename)

If your gaps are larger, put more selects in the d subquery, or use a calendar table (the latter is much easier).

Here is the SQL Fiddle.

Upvotes: 3

fancyPants
fancyPants

Reputation: 51908

select 
  date(timestamp) as my_date,
  sum(amount)
from TableName
where id = 1
and timestamp between <start_date> and <end_date>
group by my_date

In between use the date() function again, if you're passing timestamps.

Upvotes: 1

Related Questions