Difficulties with setting up a difficult mySQL query

Question

I am trying to make a query to get data from a database, but I can't get it done.

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 query that:

• Sorts the date by Year, then Month and then the Day
• Then it should count how many time of every month in these years there are.

If you don't get what I mean: It should give a result something like this (using the 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 this is to much for me. :)

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