Problems with MONTHNAME() on MySQL 5

Question

I'm trying to run a report on our database. We want to know the new registrations per industry per month. I've written this query:

SELECT 
  COUNT(j.jobseeker_id) as new_registrations, 
  i.description as industry_name,
  MONTHNAME(j.created_at)
FROM
  tb_jobseeker as j, tb_industry as i
WHERE 
  YEAR(j.created_at) = 2009 
AND 
  i.industry_id = j.industry_id 
GROUP BY 
  i.description, MONTHNAME(j.created_at)
HAVING
  MONTHNAME(j.created_at) =  MONTHNAME(NOW());

When I run this query, I get an empty result set. However, if I run the following:

SELECT 
  COUNT(j.seeker_id) as new_registrations, 
  i.description as industry_name,
  MONTHNAME(j.created_at)
FROM
  tb_seeker as j, tb_industry as i
WHERE 
  YEAR(j.created_at) = 2009 
AND 
  i.industry_id = j.industry_id 
GROUP BY 
  i.description, MONTHNAME(j.created_at)
HAVING
  MONTHNAME(j.created_at) =  'June';

It returns the results I'm looking for.

Any help please? I'm stumped.

Update: the query will be run at the end of every month or start of the next for the past month. So, we're now in June, but it needs to run for May. Hope that makes sense.

Answer 1

What does:

SELECT MONTHNAME(NOW())

Return on your server?

Answer 2

Errr.... it's not May O_o

Your query will be much more efficient if you write it like this:

SELECT 
  COUNT(j.jobseeker_id) as new_registrations, 
  i.description as industry_name,
  MONTHNAME(j.created_at)
FROM
  tb_jobseeker as j, tb_industry as i
WHERE 
  j.created_at BETWEEN '2009-05-01' AND '2009-05-31'
AND 
  i.industry_id = j.industry_id 
GROUP BY 
  i.description, MONTH(j.created_at)

You should only use HAVING if you absolutely HAVE to.

It would be even better if you could group by i.id instead of i.description, but that depends on whether i.description is unique.

Answer 3

This query could be better written:

SELECT 
 COUNT(j.jobseeker_id) as new_registrations, 
 i.description as industry_name,
 MONTHNAME(j.created_at)
FROM
  tb_jobseeker as j, tb_industry as i
WHERE 
  YEAR(j.created_at) = YEAR(NOW())
  AND MONTH(j.created_at) =  MONTH(NOW())
  AND i.industry_id = j.industry_id 
GROUP BY 
  i.industry_id

The "HAVING" clause is applied after grouping; you'd want to apply it if you wanted to filter based on the results of the aggregate (for instance, get all industries where > 100 people applied last month). Grouping by industry_id (which I assume is the primary key) is faster than grouping by a string (industry_name and monthname). And finally, don't need to group by month if you are only selecting single month.

However, you should be getting correct data, assuming your table has information for the actual current month.