CODEWITHSUNDEEP
sqlmysqlgroup-bycontiguous
joun
joun

Reputation: 664

How to returns 12 months in sql?

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}]}]}

Upvotes: 0

Views: 90

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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.

Upvotes: 0

Related Questions