Group by date?

[Erindesign]

Hey all,

I have a script that keeps track of my spendings during the day. Each spending will have a NOW() Datetime stamp.

If I was to do a "summary", I would want all spendings to addup for each day. How would I accomplish this?

ex:
ID | Amount | Datetime
1 | 25 | 2010-04-03 04:08:44
2 | 30 | 2010-04-03 03:25:12
3 | 15 | 2010-04-06 04:07:59
4 | 90 | 2010-04-06 04:08:44
5 | 10 | 2010-04-06 04:08:44

output:
Date | Amount
2010-04-03 | 55
2010-04-06 | 115

I don't know how many instances per date, I don't know if each date has an instance.

[Fumigator]

You can GROUP BY the datetime, using only the date portion. Then SUM the amount column.


SELECT DATE(datetime_column), SUM(amount)
FROM your_table
GROUP BY DATE(datetime_column)

[Erindesign]

$startdate="2010-04-01"; //april 1st
$enddate="2010-04-03"; // april 3rd

$query = "SELECT * FROM table WHERE date >= '$startdate' AND date <= '$enddatet' GROUP BY DATE(date) ORDER BY id DESC";


date=datetime NOW(); // 2010-04-01 10:11:52

Doesn't return anything. I'm thinking it is because $date is date and time, while $startdate and $enddate is only date, but I don't know how to take care of this.

[Old Pedant]

$query = "SELECT DATE(`date`) AS theDate, SUM(amount) AS total FROM table "
. " WHERE DATE(`date`) >= '$startdate' AND DATE(`date`) <= '$enddate' "
. " GROUP BY DATE(`date`) ORDER BY DATE(`date`) ASC";

Or, if you bump $enddate by 1, you can do:

$query = "SELECT DATE(`date`) AS theDate, SUM(amount) AS total FROM table "
. " WHERE `date` >= '$startdate' AND `date` < '$day_after_enddate' "
. " GROUP BY DATE(`date`) ORDER BY DATE(`date`) ASC";

Since you used a field name that is a keyword, you should *always* put the `...` around it to make sure it is interpreted correctly.

NOTE: If your `date` field is indexed, then the second version there will execute somewhat faster.