CODEWITHSUNDEEP
phpmysql
MikeOscarEcho
MikeOscarEcho

Reputation: 548

Counting number of jobs for each month

I'm running this query to get the delivery date for jobs. Each job has a leader and I'm running this query to display all jobs for each leader and the delivery date for each job.

SELECT job_num, 
       firstname, 
       lastname,
       extra_date 
FROM view_job_info 
INNER JOIN view_leader_users ON view_job_info.mm_id = view_leader_users.id
  WHERE (DATEDIFF(NOW(), latest_workorder_date) < 200  //This is a check for active jobs
     OR (DATEDIFF(NOW(), posted_date) < 200))          //Same as above
     AND job_num > 2000 AND firstname LIKE 'mike'
     ORDER BY mm_id, deliverydate DESC

Sample output:

Image

So how can I get the number of jobs for each month for Mike.

So if you look at the table, there are 4 jobs for February and I'm trying to display this on the page as

Jobs for user Mike Easter:

I tried using count() on extra_date but that just gives me the total number of rows for Mike.

Upvotes: 0

Views: 111

Answers (2)

bitfiddler
bitfiddler

Reputation: 2115

You need to use a group by with your count. Something like:

select name, count(*) from view_job_info
  where name = 'mike'
  group by MONTH(extra_date)

Upvotes: 1

jdcookie
jdcookie

Reputation: 121

Try adding

GROUP BY firstname, MONTH(extra_date)

Upvotes: 1

Related Questions