mysql select sum group by date

Question

Quick question, I have the following table

+-------------+---------------------+
| total       | o_date              |
+-------------+---------------------+
|          35 | 01-11-2009 19:32:44 | 
|        41.5 | 01-12-2009 22:33:49 | 
|        61.5 | 01-23-2009 22:08:24 | 
|          66 | 02-01-2009 22:33:57 | 
|       22.22 | 02-01-2009 22:37:34 | 
|       29.84 | 04-20-2009 15:23:49 | 
+-------------+---------------------+

I would like to add up the total for each month and group the total by month. So for instance Jan-> 138 Feb-> 88.2 Apr-> 29.84

Any clues about it. Thanks

score 95 · Accepted Answer · answered Jun 02 '09 at 02:17

95

This solution will give you the month name as a column of your resultset, followed by the total as required.

SELECT MONTHNAME(o_date), SUM(total) 
FROM theTable
GROUP BY YEAR(o_date), MONTH(o_date)

answered Jun 02 '09 at 02:17

Simon Fox

10,409
7
60
81

You may also want to add the year to the select list -> YEAR(o_date) – Simon Fox Jun 02 '09 at 02:20
2

Or, to get it in the format you specified, you can do SELECT CONCAT(MONTHNAME(o_date), '-> ', SUM(total)) – James Skidmore Jun 02 '09 at 02:52
Very usefull! and u can add columns to group by – Daniel Díaz Aug 24 '17 at 10:51

score 12 · Answer 2 · answered Jun 02 '09 at 02:15

12

select year(o_date), month(o_date), sum(total)
from table
group by year(o_date), month(o_date);

answered Jun 02 '09 at 02:15

Todd Gardner

13,313
39
51

That's assuming you want months in different years grouped seperately – Todd Gardner Jun 02 '09 at 02:20

score 7 · Answer 3 · edited Mar 30 '15 at 11:03

7

Get Month And Year wise data from MySQL database:

SELECT MONTHNAME(o_date), YEAR(o_date), SUM(total) 
FROM the_table 
GROUP BY YEAR(date), MONTH(date)

edited Mar 30 '15 at 11:03

Dmitriy

5,525
12
25
38

answered Mar 30 '15 at 10:20

Fazlul Haq

71
1
1

score 2 · Answer 4 · answered Jun 19 '18 at 20:04

2

SELECT year(FROM_UNIXTIME(created)) year, month(FROM_UNIXTIME(created)) month, sum(amount) total
FROM {my_table}
GROUP BY year, month
ORDER BY year, month;

answered Jun 19 '18 at 20:04

hugronaphor

948
8
23

score 2 · Answer 5 · answered Jun 02 '09 at 02:15

2

SELECT SUM(total)
FROM table
GROUP BY MONTH(o_date)

answered Jun 02 '09 at 02:15

James Skidmore

49,340
32
108
136

score 0 · Answer 6 · edited Mar 20 '14 at 09:41

0

Try Group BY, like this:

select count(A_id) As Tid,Day(CrDate) from Ap Group By Day(CrDate)

edited Mar 20 '14 at 09:41

gpgekko

3,506
3
32
35

answered Mar 20 '14 at 09:20

umang

1

mysql select sum group by date

6 Answers6

Linked