joun joun - 3 years ago 154
MySQL Question

How to put the month name in sequence?

I have drawn a chart like this:

enter image description here

How I want to arrange the month in sequence from january to december?

And how I want to insert month with no data inside the chart?

This is my sql.

SELECT ljj.job_id
, SUM(CASE WHEN ljj.job_type = "0" THEN 1 ELSE 0 END) AS jobcount
, SUM(CASE WHEN ljj.job_type = "1" THEN 1 ELSE 0 END) AS interncount
, MONTHNAME(FROM_UNIXTIME(ljj.job_timepublished)) AS month
FROM {local_jobs_job} ljj
INNER JOIN {local_jobs_location} ljl ON ljj.job_location = ljl.location_id
INNER JOIN {local_companydetail} lc ON ljj.job_company_userid = lc.userid
WHERE lc.link = "1"
GROUP BY MONTHNAME(FROM_UNIXTIME(ljj.job_timepublished))

Answer Source

Replace GROUP BY MONTHNAME(FROM_UNIXTIME(ljj.job_timepublished)) with GROUP BY MONTH(FROM_UNIXTIME(ljj.job_timepublished)) should do the trick

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download