sqlite3

Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

SQL combining GROUP BY and SUM

up vote 0 down vote favorite

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.02009|06|Type1|-100.02009|06|Type2|-100.02009|07|Type1|-214.912009|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.02009|06|Type2|-100.02009|07|Type1|-214.912009|07|Type2|-100.0

Yes, type should be a foreign key, I simplified things to make it easier to ask the question :)

sql sqlite3

share|improve this question

asked Oct 28 '09 at 20:26 pojo 1,48111631

sum up for each Year-Month or just for Month? –  Raj More Oct 28 '09 at 20:29

Sorry, year-month, clarified now. –  pojo Oct 28 '09 at 20:31

1 Answer

active oldest votes

up vote 6 down vote accepted

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

share|improve this answer

answered Oct 28 '09 at 20:28 ChaosPandion 33k452101

2

Careful when using ordinals for GROUP BY (and ORDER BY) - if the column order changes, there will be impact. –  OMG Ponies Oct 28 '09 at 20:32

Yes, laziness can come back to bite you. –  ChaosPandion Oct 28 '09 at 20:34

Many thanks, worked like a charm! –  pojo Oct 28 '09 at 20:36