SQL combining GROUP BY and SUM

Question

I need help with SQL. I have such a sqlite table,

CREATE TABLE mytable (datetime DATE, type TEXT, amount REAL) 

I need a query that sums up the amount for each type AND year-month (as you can see, the year is also retrieved, since the data can span several years). I came for something halfway, but I'm a little rusty in SQL.

 sqlite> SELECT strftime('%Y',datetime) AS year, strftime('%m',datetime) AS month, type, amount FROM mytable ; 2009|06|Type1|-1000.0 2009|06|Type1|-100.0 2009|06|Type2|-100.0 2009|07|Type1|-214.91 2009|07|Type2|-485.0 

I tried several combinations of SUM and GROUP BY in my query above, but none of them do what I want. What I want is the result:

 2009|06|Type1|-1100.0 2009|06|Type2|-100.0 2009|07|Type1|-214.91 2009|07|Type2|-485.0 

Yes, the type must be a foreign key, I simplified everything to simplify the question :)

Answer 1

 SELECT strftime('%Y',datetime) AS year, strftime('%m',datetime) AS month, type, Sum(amount) As Amount FROM mytable Group By 1, 2, 3 

Note

Some databases do not support a group by index, so you will need to do this.

 SELECT strftime('%Y',datetime) AS year, strftime('%m',datetime) AS month, type, Sum(amount) As Amount FROM mytable Group By strftime('%Y',datetime), strftime('%m',datetime), type