How to returns 12 months in sql?

Question

I draw a chart from this 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"

Currently, it returns only the month recorded in the ljj.job_timepublished. I want to display all months from January to December. If it doesn't have data, it will give 0 values to the month.

How to do it?

This is my php file to query data:

<?php

require_once('../../config.php');

$data = optional_param('data', null, PARAM_RAW);
$key = optional_param('key', null, PARAM_RAW);
$user = optional_param('user', 0, PARAM_INT);
$id = optional_param('id', 0, PARAM_INT);

global $DB;

///query by location total post
$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 MONTH(FROM_UNIXTIME(ljj.job_timepublished))';


//get the query into record
$data = $DB->get_records_sql($sql);

//put the query into array
$rows = array();

$rows = array_map(function($item) {
return (object) ['c' => [
    (object) ['v' => $item->month, 'f' => null],
    (object) ['v' => intval($item->jobcount), 'f' => null],
    (object) ['v' => intval($item->interncount), 'f' => null]
]];
}, array_values($data));


// prepare return data
$cols = [
(object) ['id' => '', 'label' => 'Month', 'pattern' => '', 'type' => 'string'],
(object) ['id' => '', 'label' => 'Job', 'pattern' => '', 'type' => 'number'],
(object) ['id' => '', 'label' => 'Internship', 'pattern' => '', 'type' => 'number'],

];

$returndata = new stdClass;
$returndata->cols = $cols;
$returndata->rows = $rows;

echo json_encode($returndata);

I call the datatable from the php file using ajax call to draw the chart.

This is the output of the sql query.

{"cols":[{"id":"","label":"Month","pattern":"","type":"string"}, 
{"id":"","label":"Job","pattern":"","type":"number"},
{"id":"","label":"Internship","pattern":"","type":"number"}],
"rows":[{"c":[{"v":"July","f":null},{"v":6,"f":null},{"v":2,"f":null}]},
{"c":[{"v":"August","f":null},{"v":0,"f":null},{"v":3,"f":null}]}]}

you could left-join it to a pre-built static "months" table, as a simple fix. — ADyson, Oct 02 '17 at 10:30
Either of the two answers below should fix your problem and you should mark one correct. If you have another question, then go ahead and ask it in a new question. — Tim Biegeleisen, Oct 03 '17 at 06:22

Tim Biegeleisen · Answer 1 · 2017-10-02T10:40:14.753

1

You can use a calendar table to introduce every month into your result, and left join it to what you currently have, as a subquery. I also think that you should be aggregating your data by month, which you are not currently doing. Keeping all of this in mind, we can write the following query:

SELECT
    t1.monthname,
    COALESCE(t2.jobcount, 0)    AS jobcount,
    COALESCE(t2.interncount, 0) AS interncount
FROM
(
    SELECT 'January' AS monthname UNION ALL
    SELECT 'February'  UNION ALL
    SELECT 'March'     UNION ALL
    SELECT 'April'     UNION ALL
    SELECT 'May'       UNION ALL
    SELECT 'June'      UNION ALL
    SELECT 'July'      UNION ALL
    SELECT 'August'    UNION ALL
    SELECT 'September' UNION ALL
    SELECT 'October'   UNION ALL
    SELECT 'November'  UNION ALL
    SELECT 'December'
) t1
LEFT JOIN
(
    SELECT
        MONTHNAME(FROM_UNIXTIME(ljj.job_timepublished)) AS monthname,
        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   
    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))
) t2
    ON t1.monthname = t2.monthname;

Note that as @Gordon pointed out, a more sensible aggregation might include both the month and year. But that would make the calendar table more complex and might require a script to generate it.

edited Oct 02 '17 at 10:40

answered Oct 02 '17 at 10:38

Tim Biegeleisen

502,043
27
286
360

`SELECT 'December' UNION ALL` seems to be syntax error ? – Ravi Oct 02 '17 at 10:39
1

@ravi Of course it is; hard to not make a mistake when secretly using SO on a cell phone :-) – Tim Biegeleisen Oct 02 '17 at 10:40
got this error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM( SELECT "January" AS monthname UNION ALL SELECT "February" UNION' at line 2 SELECT t1.monthname, COALESCE(t2.jobcount, 0) AS jobcount, – joun Oct 02 '17 at 11:06
What you are quoting is not the raw query in my answer which, AFAIK, is valid and should run. If you are running this from an app language such as PHP or Python, then you need to take care that you preserve what I wrote in my answer. – Tim Biegeleisen Oct 02 '17 at 11:09
@joun Well I would need to see your PHP. Ideally you would be using prepared statements. – Tim Biegeleisen Oct 03 '17 at 01:36
I put the code and output in the edited question. Thanks @Tim Biegeleisen – joun Oct 03 '17 at 01:57
The code you pasted contains some other query, I can't help with that. I believe my answer to be valid and you should try to make it work for you. – Tim Biegeleisen Oct 03 '17 at 02:23
Or can I create a dummy array of months and push it to the json string? – joun Oct 03 '17 at 03:07
Do you have any idea what cause the error, @Tim Biegeleisen? The select month doesn't mean create new table, right? – joun Oct 03 '17 at 06:18

score 0 · Answer 2 · answered Oct 02 '17 at 10:33

Add a GROUP BY. I would recommend:

GROUP BY YEAR(From_unixtime(ljj.job_timepublished)), 
         MONTH(From_unixtime(ljj.job_timepublished))

However, your code is only using the MONTHNAME(), so combining data from different years seems to be your intention. If that is really your intention:

GROUP BY Monthname(From_unixtime(ljj.job_timepublished))

Otherwise put the year in the SELECT.

How to returns 12 months in sql?

2 Answers2