MySQL: how do I add up values if the occur on the same day?

Question

I have a column of datetimes, and a column of values. How do I add up all teh values that occur on the same day? so like... midnight:01 to 23:5 => add all the records that occur in that time period.

then group by day.

bit hard to explain. sadness.

My crystal ball tells me that your next question [has already been answered on SO](http://stackoverflow.com/questions/2157282/generate-days-from-date-range)... — pilcrow, Aug 20 '10 at 15:51
@pilcrow: That question is for date *generation* - this for *summation* of values over a given date. — OMG Ponies, Aug 20 '10 at 15:55
Right Ponies, and the very common follow-up is, how do I summarize those dates absent from my original data set. — pilcrow, Aug 20 '10 at 15:56

score 4 · Accepted Answer · answered Aug 20 '10 at 15:39

4

Use:

SELECT SUM(t.value_column)
  FROM TABLE t
GROUP BY DATE(t.datetime_column)

The DATE function only captures the year/month/day portion - time is ignored, so anything on that date will be grouped together.

answered Aug 20 '10 at 15:39

OMG Ponies

314,254
77
507
490

score 1 · Answer 2 · answered Aug 20 '10 at 15:40

1

In MySQL:

SELECT  CAST(datetime_field AS DATE) AS date_field, SUM(value)
FROM    mytable
GROUP BY
        date_field

answered Aug 20 '10 at 15:40

Quassnoi

398,504
89
603
604

MySQL: how do I add up values if the occur on the same day?

2 Answers2