CODEWITHSUNDEEP
mysqlsqldategroup-bysql-order-by
Undefined Variable
Undefined Variable

Reputation: 4267

Group by year in date field in 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.

Upvotes: 4

Views: 10378

Answers (3)

Barmar
Barmar

Reputation: 781300

Use the YEAR function to get the year part of the date, and use that in the grouping and ordering.

SELECT YEAR(date) AS year, COUNT(*) AS customer_count
FROM customer
GROUP BY year
ORDER BY year

Upvotes: 1

bluefog
bluefog

Reputation: 1894

Use the EXTRACT function to get the relevant part of the date : 

select extract(YEAR from join_d)"Year",count(DISTINCT customer_id) "customer_count" 
from  customer 
group by year
order by year;

Upvotes: 0

juergen d
juergen d

Reputation: 204784

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

Upvotes: 8

Related Questions