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
解决方案
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)