Thursday, February 6, 2014

Group by date time month or year and sum in SQLite

I have a database table that contains a few fields like transaction date which is a string representing date time, transaction amount, and transaction status in a SQLite database present on an Android device. The SQL Lite database contains a table named "trx_log", the table has fields _id int, trxDateTime text, trxTargetNumber text, trxAmount number, trxStatus text. A graphical representation of the table structure is given below:
sqlite-android-search-by-year-group-by-month

SQLLite Query Sum and Group By Year

How to calculate sum of all transactions that took place in the year 2014? What is the SQLite query for group by results by year and calculating a sum? We are going to use SQLite aggregate function SUM in and combine it with GROUP BY Clause

SELECT strftime('%Y', trxDateTime) as valYear, SUM(trxAmount) 
FROM trx_log WHERE valYear = '2014' GROUP BY valYear