mysql中年的,在MySQL中按年份排列

I have a MySQL database which has a customer table. Some dummy data is:

customer_id date

000001 2008-10-10

000002 2008-11-11

000003 2010-01-02

000004 2007-04-03

000005 2010-05-05

I want to run a query which will give me a result like so:

year customer_count

2007 1

2008 2

2010 2

I know I need to use group by, however I am unable to wrap my head around how to group based on year value of a date field, and how to have them in an order.

解决方案

Extract the year from the date and group by it

select year(date) as year,

count(customer_id) as customers

from your_table

group by year

order by year asc