Thursday, February 6, 2014

GROUP BY Date Time, Day, Month, Year, calculate SUM function in SQLite for app developers


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

Given above is the query, which will be explained in the blog below; continue reading for further details.

SQLite GROUP BY Day, Month, Year and sum query example

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 an 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, and trxStatus text. A graphical representation of the table structure is given below:

sqlite-android-search-by-year-group-by-month

SQLite Table used in the example of GROUP BY date, time, month, year, and SUM function.

SQLite Query Sum and Group By Year

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

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