Geek asks and answers

Mysql selects the number of records for each month

I need to process a query in mysql that will return 12 rows (one row for each month), which selects the name of the month, and the number of records for a given month. I have two tables: month_tbl and events_tbl. Each entry in event_tbl has a datetime column and a company_id column.

I'm currently doing something like (note that there is no 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 expect (12 rows selected, number of events per month, or zero if 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 returns all records regardless of company. I need to make sure the request is filtered this way, I added a 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 a where clause, my results become:

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

, , where, ?

+5
1

LEFT JOIN, where "" JOIN

:

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;

, AND events_tbl.company_id = 1 LEFT JOIN

+6

More articles:


All Articles