MySQL query to return an avg price per month(get the month out of the date formated 2010-03-30 for example)

I have three tables...

Given the following tables I would like to know how to write a query to return all the book sorted by month and with a average price per month table (that would be somethin开发者_开发知识库g like SUM(invoices.total)/COUNT(invoices.total). I have a date field which has a format of 2010-03-30, and I would like to get the months out of if…so I can sort the sum by month. I'm using PHP and MySQL. Thank you in advance!

books

-call_started

books_chapters

-id

invoices

-id

-invoice_type

-chapters_id

-total

---

I can not construct the full query from the schema in your question but you can group the results - GROUP BY(CONCAT(YEAR(date_field), MONTH(date_field))

If you elaborate or comment, I will improve my answer.