How to find Sum of a field per month in a particular in sqlite3

1
votes

I have a table created by the following statement in sqlite3

CREATE TABLE IF NOT EXISTS expenses 
  ( 
     id       INTEGER PRIMARY KEY, 
     name     TEXT, 
     amount   REAL, 
     category INT NOT NULL, 
     date     TEXT 
  )

date entered in the database is in the format yyyy-mm-dd.

How to do i write a sqlite3 select statement such that i get sum of expenses per month in a given year. In other words if the user enters year 2011 I should get total expenses per month in 2011. 

month  total_amount
1       200
2       10
3       1500
4       340
5       124
1
sqlite

1 Answers

2
votes
SELECT SUM(amount)            AS total_amount, 
       Strftime("%m", `date`) AS 'month' 
FROM   expenses 
WHERE  Strftime("%Y", `date`) = '2011'
GROUP  BY Strftime("%m", `date`);

check out SQLite Date And Time Functions

(edited)