Aggregating MySQL rows by day

Question

Hi,

I have a MySQL table like this:

Payments
+----+---------------------+---------+
| id | date (sorted DESC)  | payment |
+----+---------------------+---------+
|  5 | 2010-03-26 00:00:01 |  100.00 |
|  4 | 2010-03-19 00:00:02 |   55.12 |
|  3 | 2010-03-19 00:00:01 |   20.00 |
|  2 | 2010-03-12 00:00:02 |  320.00 |
|  1 | 2010-03-12 00:00:01 |   64.56 |
+----+---------------------+---------+

How can I aggregate the total payments by day?

Payments are inserted into the table every week, but they aren't always the exact same time as you can see. Can aggregation by day be done natively in MySQL?

The desired result would be:

+------------+----------------+
| date       | total payments |
+------------+----------------+
| 2010-03-26 |         100.00 |
| 2010-03-19 |          75.12 |
| 2010-03-12 |         384.56 |
+------------+----------------+

Thanks in advance.

Answer 1

You can do 2 things, change the datetype to date, so just storing the date (this might not be possible in your situation). Or you can group by reforming the date value so its just the date.

select date(date), sum(payment)
from payments
group by date(date)
order by date(date) desc

Answer 2

You can group by a function call on the date, so convert the full datetime to just the date part and group by that, so long as the date is in the select, it should be legal.

Answer 3

SELECT CONVERT(VARCHAR(10), date , 102) as date,sum(payment ) as total_payment
FROM Payments
GROUP BY  CONVERT(VARCHAR(10), date , 102) 
ORDER BY  CONVERT(VARCHAR(10), date , 102)

should be ok