1

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

jpmc26
  • 28,463
  • 14
  • 94
  • 146
conbask
  • 9,741
  • 16
  • 57
  • 94
  • I'm afraid this is not possible with your data structure since the dates (that are being skipped) come from rows inside your table. If you don't have rows, you don't have dates. – tucaz Feb 04 '14 at 01:03
  • What database are you using? SQL Server? MySQL? PostgreSQL? And what data type is `timestamp`? – jpmc26 Feb 04 '14 at 01:06
  • `timestamp` is INT, it's a mySQL db. – conbask Feb 04 '14 at 01:07
  • possible duplicate of [Select Range of Dates, Including Ones With No Results](http://stackoverflow.com/questions/12484349/select-range-of-dates-including-ones-with-no-results) – Mike M Feb 04 '14 at 01:34

2 Answers2

1

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

Moho
  • 15,457
  • 1
  • 30
  • 31
1

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.

jpmc26
  • 28,463
  • 14
  • 94
  • 146
  • Why do you say "if this a web application..."? What is so special about web applications? – LoztInSpace Feb 04 '14 at 22:50
  • @LoztInSpace Fine, if you want to nit-pick unnecessary, "if there is some kind of code sitting on top of this database" instead. Happy? – jpmc26 Feb 04 '14 at 22:52