SQL combining GROUP BY and SUM

Question

I need help with SQL. I have an sqlite table like so;

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

I need a query which would sum up amount for each type AND year-month (as you can see the year is also extracted since the data can span several years). I've come to something half-way, but I'm a bit rusty on 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've tried a number of combinations of SUM and GROUP BY on my query above but none of them does what I want. What I want is a result something like:

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

Yes, type should be a foreign key, I simplified things to make it easier to ask 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 DBs don't support group by index so you would have 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