Query returns null instead of 0

Question

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:

Desired Results:

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

So you're trying to select data that doesn't actually exist and you want it to be in the results :) You will have to first create that data and then left join it. Check this [question](http://stackoverflow.com/questions/10034668/how-to-generate-data-in-mysql) — Mosty Mostacho, Dec 13 '13 at 18:10
As a side note, take into account that not returning a result is very different than returning `null` as you stated in your question — Mosty Mostacho, Dec 13 '13 at 18:11
http://stackoverflow.com/questions/273623/mysql-select-from-a-list-of-numbers-those-without-a-counterpart-in-the-id-fiel — PasteBT, Dec 13 '13 at 18:12

score 0 · Answer 1 · answered Dec 13 '13 at 18:09

To get your query to return a row for July, you need to have a row with July in it. You could create a table with all the dates between $lastyear and $date in it and then outer join from that to SERVICE_JOB.

SELECT COUNT( WORK_ID_NUM ) AS count, 
       allDates.DATE 
FROM AllDates 
Left outer join SERVICE_JOB
on AllDates.DATE = SERVICE_JOB.DATE
WHERE (AllDates.DATE BETWEEN '$lastyear' AND '$date') AND
(SERVICE_JOB.WORK_ID_NUM is NULL OR JOB_TYPE LIKE 'Street Light')
GROUP BY YEAR( AllDates.DATE ), MONTH( AllDates.DATE ) 
ORDER BY AllDates.DATE

In SQL Server it would be pretty easy to make a Common Table Expression that could fill AllDates for you based on $lastyear and $date. Not sure about MySql.

I did that and the results in 0 rows ... I created my AllDates table as follows: ID | DATE and then I have an integer as the ID and 2012-12-01 - 2014-01-01 as the date range. What have I done wrong? Thanks! — user3100370, Dec 13 '13 at 18:37

score 0 · Accepted Answer · answered Dec 13 '13 at 19:14

0

sqlFiddle Demo

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;

answered Dec 13 '13 at 19:14

Tin Tran

6,194
3
19
34

it works but it's a hack (assuming you always have more than 13 rows of data in service_jobs) – Tin Tran Dec 13 '13 at 20:24
1

I am fine with a hack! It works! And yes there will always be a lot more than 13! Thanks again! – user3100370 Dec 13 '13 at 20:59
Sure, but I don't know how to accept an answer. – user3100370 Dec 16 '13 at 14:10

Query returns null instead of 0

2 Answers2