Menu
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
216
In SQL Server, you can use DATEPART(QUARTER,whn) and YEAR(whn) . In Oracle, you can use TO_CHAR(whn,'Q') and TO_CHAR(whn,'YYYY') for the quarter and year.
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
You can use DATE() from MySQL to select records with a particular date. The syntax is as follows. SELECT *from yourTableName WHERE DATE(yourDateColumnName)='anyDate'; To understand the above syntax, let us first create a table.
Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database.
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)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
