how to group MySql rows based on month?

2
votes

I've a table with a datetime (format: 'Y-m-d H:i:s') 'created' field and 'amount' (integer) field in each row. Now I want to find out month wise total 'amount' in last year. How can I do this?

EDIT

I made an edit to clarify the actual problem. so basically I want to know total 'amount' in each month, but only for the last year.

EDIT2

Last year means last 365 days. So somewhere I've to consider 'current day'?

EDIT3

My bad, actually last year is last 12 months. So number of days would be between 336 and 365.

3
phpmysqlgroup-by

3 Answers

8
votes

Try this (updated to answer your "edit3"):

SELECT
    YEAR(created) as year,
    MONTH(created) AS month,
    SUM(amount) AS total_amount
FROM table1
WHERE created
    BETWEEN DATE(NOW()) - INTERVAL (DAY(NOW()) - 1) DAY - INTERVAL 11 MONTH
    AND NOW()
GROUP BY YEAR(created), MONTH(created)
ORDER BY YEAR(created), MONTH(created);

Example result (when run in April 2010):

year  month  total_amount
2009  5      26
2010  1      20

Note also that months with no entries will not be returned at all (rather than being returned with total_amount = 0).

Test data:

CREATE TABLE table1 (created datetime NOT NULL, amount INT NOT NULL);
INSERT INTO table1 (created, amount) VALUES
('2010-01-01 13:56:23', 5),
('2010-01-04 13:56:23', 15),
('2009-05-04 13:56:23', 26);
2
votes

This returns the count and total amount for last year:

SELECT MONTH(created) as month_updated,
  COUNT(created) as month_updates, SUM(amount) as month_total FROM table
WHERE created BETWEEN DATE_ADD(NOW(), INTERVAL -1 YEAR) AND NOW()
GROUP BY MONTH(created)

Or, if you specifically mean just 2009:

SELECT MONTH(created) as month_updated,
  COUNT(created) as month_updates, SUM(amount) as month_total FROM table
WHERE created BETWEEN '2009-01-01 00:00:00' AND '2009-12-31 23:59:59'
GROUP BY MONTH(created)
0
votes
SELECT count(some_row) AS or_whatever FROM your_table GROUP BY MONTH(update);

To be more specific (with your update):

SELECT SUM(amount) FROM table_name GROUP BY MONTH(created);