sqlite3

quasiceo 2013-11-16

展开全文

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.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|-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