mysql select the count of records for every month

Question

I need to craft a query in mysql that will return 12 rows (one row for each month) that selects the month name, and the count of records for the given month. I have two tables, a months_tbl and events_tbl. Each record in the events_tbl has a datetime column and a company_id column.

I'm currently doing something like (notice I don't have the WHERE company_id clause yet):

SELECT months_tbl.month, COUNT( events_tbl.event_id ) cnt
FROM months_tbl
LEFT JOIN events_tbl ON months_tbl.month_id = MONTH( events_tbl.appt_date_time )
GROUP BY months_tbl.month
ORDER BY months_tbl.month_id ASC;

This returns something like what I'm expecting (12 rows selected, with a count of events for the month, or zero if there were none):

**month**    **cnt**
January      0
February     0
March        0
April        0
May          0
June         0
July         0
August       88
September    99
October      120
November     0
December     9

however it's returning all records regardless of company. I need to make sure the query is filtered by so, I added the where clause:

SELECT months_tbl.month, COUNT( events_tbl.appt_id ) cnt
FROM months_tbl
LEFT JOIN events_tbl ON months_tbl.month_id = MONTH( events_tbl.appt_date_time ) 
WHERE events_tbl.company_id = 1 
GROUP BY months_tbl.month
ORDER BY months_tbl.month_id ASC;

When I add the where clause my results become:

**month**    **cnt**
August       88
September    99
October      120
December     9

Any ideas why I'm losing all the other months records when I add the where clause, even though I'm using a left join?

Answer 1

You are using a LEFT JOIN, but in your where statement your making it a 'normal' JOIN

Try writing it like this:

SELECT months_tbl.month, COUNT( events_tbl.appt_id ) cnt
FROM months_tbl
  LEFT JOIN events_tbl ON (months_tbl.month_id = MONTH(events_tbl.appt_date_time) 
    AND events_tbl.company_id = 1
  )
GROUP BY months_tbl.month
ORDER BY months_tbl.month_id ASC;

Note the AND events_tbl.company_id = 1 is in the LEFT JOIN