Group by month in the date field (string)

Question

A table with a string field where date is being stored (don't suggest me to change the field type. It will cause hazard).

+--+----------+------+
|id|date      |amount|
+--+----------+------+
|1 |23-03-2014|5000  |
+--+----------+------+
|2 |25-03-2014|3000  |
+--+----------+------+
|3 |21-04-2014|3000  |
+--+----------+------+
|4 |25-04-2014|4000  |
+--+----------+------+

I want to write a model function which will return sum of amount grouped by month from the table. How should I do it? It will show output like

03 - 8000
04 - 7000

Is this work for you? http://stackoverflow.com/a/508806/685060 — lighter, Oct 15 '14 at 10:02
I think not, cause my date field is string type and changing this will cause me a lot of work — S. M. Shahinul Islam, Oct 15 '14 at 10:03
date is string so mysql date like MONTH() function won't work. As you insisted I tried. But it shouldn't and hasn't worked — S. M. Shahinul Islam, Oct 15 '14 at 10:18
is your data stored in a contineous format (ie) in the order of dates — Ronser, Oct 15 '14 at 10:18
convert string type to date http://stackoverflow.com/a/1861551/685060 — lighter, Oct 15 '14 at 10:20
are you interested in converting and updating the 'string date' as date or you just want to group them for your desired result?? — Ronser, Oct 15 '14 at 10:22

score 1 · Accepted Answer · 2014-10-15T10:20:45.493

1

SELECT MONTH(date_field) as mon, SUM(amount) as total FROM table_name GROUP BY MONTH(date_field);

Update:

SELECT SUBSTR(date_field,6,2) as mon, SUM(amount) as total FROM table_name GROUP BY mon;

edited Oct 15 '14 at 10:20

answered Oct 15 '14 at 10:06

Sorry this doesn't work as I mentioned earlier that date is a string field. – S. M. Shahinul Islam Oct 15 '14 at 10:13
@Alvi_1987 Edited. If it has helped then can you please accept my answer? – Oct 15 '14 at 10:48

score 0 · Answer 2 · answered Oct 15 '14 at 10:05

0

You can try following query

SELECT SUM(amount) FROM table GROUP BY MONTH(date)

answered Oct 15 '14 at 10:05

Bhumi Shah

9,323
7
63
104

score 0 · Answer 3 · answered Oct 15 '14 at 10:05

0

Try MONTH()

SELECT MONTH(date),SUM(amount) FROM table GROUP BY MONTH(date)

answered Oct 15 '14 at 10:05

Rakesh Sharma

13,680
5
37
44

Group by month in the date field (string)

3 Answers3