SQLite query to join on a range of dates?

Question

I am working with SQLite.

Suppose I have a table sales with two columns, date and count, to keep track of how many glasses of lemonade I sold on that date. If I don't sell any lemonade on a given day, I am too depressed to create a new row in the sales table.

I'd like to find out the average number of glasses sold over a given date range. I could issue a query like this:

SELECT AVG(count) FROM sales WHERE date IS BETWEEN '2010-01-04' AND '2010-01-10';

However, the result won't be accurate unless all of the dates in that range are included in the table. I need some way to tell SQLite to count the missing rows as zeroes.

I imagine if I could join the sales table on a date range, that would be ideal. That doesn't seem to be possible, so a simple way of creating a table from a date range is probably the next best thing. I don't want to modify the sales table, which seems silly for a lemonade stand but makes more sense in the real life counterpart.

As I said above, I am using SQLite, so you are not being helpful if you recommend other databases to me. That said, if you have more experience with another database and think your solution can be done in vanilla SQL, I'd love to hear it.

Answer 1

Create a calendar table and join onto that:

Something like this will do:

create table dates (id integer primary key);
insert into dates default values;
insert into dates default values;
insert into dates select null from dates d1, dates d2, dates d3 , dates d4;
insert into dates select null from dates d1, dates d2, dates d3 , dates d4;
alter table dates add date datetime;
update dates set date=date('2000-01-01',(-1+id)||' day');

That will cover you till 2287-05-20 Adding an index to the date column in the dates table won't hurt. My sqlite is little rustly, so I suggest you consult the manual on that.

Answer 2

I think you need something like this:

SELECT sum(count) / ((strftime('%s','2010-01-10')-strftime('%s','2010-01-04')+86400)/86400)
FROM sales
WHERE date IS BETWEEN '2010-01-04' AND '2010-01-10';

The query calculates the exact number of days by taking the seconds between the given dates
and dividing with 86400 (= 24*3600) secs.

For example

SELECT (strftime('%s','2010-03-01')-strftime('%s','2010-01-01') + 86400) / 86400

outputs 60 which is the correct value.

Check out SQLite's Date And Time Functions.