Select month and sum for all days in month

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?

---

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

---

If you can narrow the date to the granularity you want in the database then you can do it one query and iterate over the results. It depends on what field type your data column is. If it's a VARCHAR for example you can use SUBSTRING to pick out the parts you want (year and month). If it's a DATETIME you can do something like this:

SELECT strftime(date, '%m-%Y'), SUM(quantity)
FROM table GROUP BY strftime(date, '%m-%Y')