Get sum of amount monthly

Question

I do not know English, sorry :(

example table:

CREATE TABLE IF NOT EXISTS `__example__` (
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`amount` decimal(10,2) DEFAULT NULL, 
`fromDate` datetime DEFAULT NULL, 
`toDate` datetime DEFAULT NULL, 
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

and rows:

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (1, 1000.00, '2016-01-16 17:05:40', '2016-01-26 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (2, 1000.00, '2016-01-26 17:05:40', '2016-02-05 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (3, 1000.00, '2015-12-26 17:05:40', '2016-01-05 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (4, 4000.00, '2015-12-26 17:05:40', '2016-02-04 17:05:45');

I want to see:

year | month | sum
2015 |    12 | 1400.00
2016 |    01 | 4700.00
2016 |    02 | 900.00

Example:

2 rows started at 12/2015 and the sum thereof is 5000.00(1000+4000).

But 12/2015 sum column should be 1400.00.

How can I do that with SQL query? Help me, please.

Answer 1

This query should accomplish your goal.

Select YEAR(fromDate) [Year], MONTH(fromDate) [Month], SUM(amount) [Amount]
FROM '__example__'
GROUP BY YEAR(fromDate), MONTH(fromDate)
ORDER BY Year, Month

Answer 2

I've still never used mysql. But Looking at your edited requirements, I THINK you're asking for something like this:

1. Take the total amount and divide it by the number of days between todate and fromdate.
2. Find the number of days in the month from the "fromdate" to the end of the month.
3. Multiply the average amount (step 1) by the number of days in each month to get the value in each month.

If that's what you're asking for then the solution is pretty easy.

You'll first need a row for each month in the span. I'd suggest creating a "DATES" table and joining Examples to the date table. Create a row in the table for every month between 2015 and 2020 (like '01/01/2015', '02/01/2015', '03/01/2015', etc.).

After this, and using the mysql LAST_DAY() and DATE_ADD functions, you should be able to calculate the average amount per day (=amount/total days).