Select month and sum for all days in month

Question

My application has users entering records into a database. The records contain an auto-incrementing id, date (as text in mm-dd-yyyy format), a quantity (int) and a few other fields.

I'm trying to grab all the months and the sum quantity for each month. So that I can display them in my app like: Dec 2010 - 90qty, Jan 2011 - 45qty...

What I'm doing now is, selecting date by id, ascending limit 1 and descending limit 1 to get the first and last dates in the database. I then split the months and year and calculate how many months are in there, then I do a for loop and fill an array so I have 12-2010, 01-2011, 02-2011...etc then another for loop to query the database for sum(quantity) where date is between 12-01-2011 and 12-31-2011, etc.

I'm wondering... is there a better way?

Ideally, dates in SQLite should be stored in ISO8601 format (YYYY-MM-DD) for easy use with date functions. — MPelletier

Mike Sherrill 'Cat Recall' Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-04-13T00:16:49

CREATE TABLE test (
id integer primary key, date date, quantity integer);
INSERT INTO "test" VALUES(1,'2011-01-01',5);
INSERT INTO "test" VALUES(2,'2011-02-01',13);
INSERT INTO "test" VALUES(3,'2011-02-12',14);
INSERT INTO "test" VALUES(4,'2011-03-01',133);

You can group by months and sum up the quantities like this.

select strftime('%Y-%m', date), sum(quantity) 
from test
group by strftime('%Y-%m', date);

2011-01    5
2011-02   27
2011-03  133

Select month and sum for all days in month

2 Answers