Difficulties with setting up a complex mySQL query

Question

I am trying to make a request to retrieve data from a database, but I cannot do this.

I have the following table with dummy data:

id | date 0 | 2011-11-25 20:12:32 1 | 2011-11-15 20:12:32 2 | 2011-11-05 20:12:32 3 | 2011-10-25 20:12:32 4 | 2011-10-15 20:12:32 5 | 2011-10-05 20:12:32 6 | 2010-10-25 20:12:32 7 | 2010-04-25 20:12:32 8 | 2009-07-25 20:12:32 

---

I want to make a request that:

• Sorts date by year, then month, then day
• Then he must calculate how many times each month in these years there are.

If you do not understand what I mean: It should give a result something like this (using dummy data from the table):

---

 Year | Month | amount (of rows with that month in that year) 2011 | 11 | 3 2011 | 10 | 3 2010 | 4 | 2 2009 | 7 | 1 

I have some knowledge of mySQL, but for me it is very important. :)

Thanks in advance

Answer 1

 SELECT YEAR(`date`) AS `year`, MONTH(`date`) AS `month`, COUNT(*) AS amount FROM `table` GROUP BY YEAR(`date`), MONTH(`date`) ORDER BY `date` DESC 

Answer 2

 SELECT YEAR(date) AS Year, MONTH(date) AS Month, COUNT(*) as amount FROM table GROUP BY LEFT(date, 7) ORDER BY date DESC 

Answer 3

Use this in your query:

 SELECT count(*) from t GROUP BY YEAR(date), MONTH(date)