Stop query from skipping over null values

I have a query that shows me the number of calls per day for the last 14 days within my app.

The query:

SELECT count(id) as count, DATE(FROM_UNIXTIME(timestamp)) as date FROM calls GROUP BY DATE(FROM_UNIXTIME(timestamp)) DESC LIMIT 14

On days where there were 0 calls, this query does not show those days. Rather than skip those days, I'd like to have a 0 or NULL in that spot.

Any ideas for how I can achieve this? If you have any questions as to what I'm asking please let me know.

Thanks

Answers


I don't believe your query is "skipping over NULL values", as your title suggests. Rather, your data probably looks something like this:

 id | timestamp
----+------------
  1 | 2014-01-01
  2 | 2014-01-02
  3 | 2014-01-04

As a result, there are no rows that contain the missing date, so there are no rows to be counted. The answer is that you need to generate a list of all the dates you want and then do a LEFT or RIGHT JOIN to it.

Unfortunately, MySQL doesn't make this as easy as other databases. There doesn't seem to be an effective way of generating a list of anything inline. So you'll need some sort of table.

I think I would create a static table containing a set of integers to be subtracted from the current date. Then you can use this table to generate your list of dates inline and JOIN to it.

CREATE TABLE days_ago_list (days_ago INTEGER);

INSERT INTO days_ago_list VALUES
 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)
;

Then:

SELECT COUNT(id), list_date
FROM (SELECT SUBDATE(CURDATE(), days_ago) AS list_date FROM days_ago_list) dates_to_list
LEFT JOIN (SELECT id, DATE(FROM_UNIXTIME(timestamp)) call_date FROM calls) calls_with_date
     ON calls_with_date.call_date = dates_to_list.list_date
GROUP BY list_date

It is very important that you group by list_date; call_date will be NULL for any days without calls. It is also important to COUNT on id since NULL ids will not be counted. (That ensures you get a correct count of 0 for days with no calls.) If you need to change the dates listed, you simply update the table containing the integer list.

Here is a SQL Fiddle demonstrating this.

Alternatively, if this is for a web application, you could generate the list of dates code side and match up the counts with the dates after the query is done. This would make your web app logic somewhat more complicated, but it would also simplify the query and eliminate the need for the extra table.


create a table that contains a row for each date you want to ensure is in the results, left outer join with results of your current query, use temp table's date, count of above query and 0 if that count is null


Need Your Help

Externalized message.properties file is not picking up in Tomcat

spring java-ee tomcat properties resourcebundle

According to my requirement, I need to externalize the message.properties file (Keep outside from war file) and in same time it should be automatically re loadable on update.