CODEWITHSUNDEEP
mysqldate
abnab
abnab

Reputation: 846

How do I group a date field to get quarterly results in MySQL?

I have a job table that holds jobs and leaddate is the field for the job entry.

The result I want to get is the number of jobs I have in each quarter. My query counts jobs of each date in the leaddate field.

Here is the query

select count(jobid) as jobcount, leaddate
from jobs
where contactid='19249'
group by leaddate

Upvotes: 22

Views: 32254

Answers (4)

Marc Maxmeister
Marc Maxmeister

Reputation: 4689

I usually combine quarter and year into a single YEAR-QUARTER field:

select jobid, count(jobid) as jobcount, 
CONCAT( YEAR(leaddate),'-',QUARTER(leaddate) ) as year_qtr
from jobs
where contactid='19249'
group by year_qtr

RETURNS data like:

19249, 324, 2011-3
19249,   4, 2011-2
19249,  34, 2011-1

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 754110

I think this should do the job:

SELECT YEAR(leaddate) AS year, QUARTER(leaddate) AS quarter, COUNT(jobid) AS jobcount
  FROM jobs
 WHERE contactid = '19249'
 GROUP BY YEAR(leaddate), QUARTER(leaddate)
 ORDER BY YEAR(leaddate), QUARTER(leaddate)

Upvotes: 36

Headshota
Headshota

Reputation: 21449

you can use the Quarter function to get a quarter from date:

select count(jobid) as jobcount, QUARTER(leaddate) as qt, YEAR(leaddate) as year
from jobs
where contactid='19249'
group by year,qt

Upvotes: 0

zerkms
zerkms

Reputation: 254944

Supposing you have a valid date or datetime field:

select count(jobid) as jobcount, YEAR(leaddate) yr, QUARTER(leaddate) qt
from jobs
where contactid='19249'
group by yr, qt

Upvotes: 6

Related Questions