MySQL Query for getting payments evolution graph during latest 12 months

My app stores payments done by clients. At the end of every month, the total paid that m开发者_JAVA技巧onth is calculated and given to owner: I have a payments table that has the following fields (the most important).

id, datepaid, endingdate (actual month's closing date), ammount, type, code, ...

Now, having a year of payments, I've been asked to create a graph of payments evolution (totals) of latest 12 months.

By reading, etc. this is the query I've got, but I don't know how to get the latest 12 months totals (and to get 0 in case no payment was done that month)....

    SELECT id, endingdate, datepaid, SUM(ammount) AS total
    FROM `sis_payments` 
    WHERE endingdate >= DATE_SUB( CURDATE( ) , INTERVAL 1 YEAR )
    GROUP BY endingdate

I know it may be badly designed, but it's what I was given... any clues? Thanks

You could try GROUP BY YEAR(endingdate), MONTH(endingdate), or something equivalent using DATE_FORMAT. Take a look at the Mysql documentation for DATE_FORMAT.

Also, don't include the date fields (endingdate, datepaid) in the SELECT clause. Instead, use YEAR(endingdate), MONTH(endingdate), just like in GROUP BY.

Getting 0 when there were no payments that month is a little more complicated in SQL. You could handle that in PHP, after running the query.

UPDATE

Example using DATE_FORMAT:

SELECT 
    DATE_FORMAT(mrendido,'%y-%m') as xyearmonth, 
    SUM(FORMAT(ammountpaid,2)) AS ammtotal 
FROM sis_pyments
WHERE mrendido >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 
GROUP BY xyearmonth 
ORDER BY xyearmonth ASC