I have a query that counts all Work ID Numbers by month and year and then creates a chart for the past 13 months using jpgraph. It works great except there are no Work ID Numbers in July so the chart totally skips July.
Query Results:
5
16
15
11
3
12
4
8
2
9
13
12
Desired Results:
5
16
15
11
3
12
0
4
8
2
9
13
12
As you can see I need the (0) zero in order for my chart to work, however since there are no Work ID Number in July my query simply skips it. Here is my query:
SELECT COUNT( WORK_ID_NUM ) AS count,
DATE FROM SERVICE_JOBS
WHERE (DATE BETWEEN '$lastyear' AND '$date')
AND JOB_TYPE LIKE 'Street Light'
GROUP BY YEAR( DATE ), MONTH( DATE )
ORDER BY DATE
SELECT IFNULL(count,0) as count,theDate as Date
FROM
(SELECT @month := @month+INTERVAL 1 MONTH as theDate
FROM service_jobs,(SELECT @month:='$lastyear' - INTERVAL 1 MONTH)as T1
LIMIT 13)as T2
LEFT JOIN
(SELECT COUNT(WORK_ID_NUM)as count,DATE
FROM service_jobs
WHERE (DATE BETWEEN '$lastyear' AND '$date')
AND JOB_TYPE LIKE 'Street Light'
GROUP BY YEAR(DATE), MONTH(DATE)) T3
ON (YEAR(theDate) = YEAR(DATE) AND MONTH(theDate) = MONTH(DATE))
ORDER BY theDate;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments