SQLLite Query Sum and Group By YearHow 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
I have used SQLite strftime function along with format string '%Y' . As a result, SQLite query returns '2014' in result set column named valYear which is used in WHERE clause and GROUP BY clause as well.
SQLLite Query Sum and Group By Month and YearNow suppose I want to calculatnce the sum of all transaction that took place in all months of 2014 and show those as a column. SQLite with SUM aggregate function and a slightly different where clause and GROUP BY will be used
SELECT strftime('%m', trxDateTime) as valMonth, SUM(trxAmount) as valTotalMonth FROM trx_log WHERE strftime('%Y', trxDateTime)='2014' GROUP BY valMonth
Please note that the data set returned by this SQLLite against this SQL query will have two result set columns valMonth and valTotalMonth, the column valMonth will have numeric values from 01 to 12.
SQLLite Query Sum and Group By Day, Month, and YearNext up we got a query to calculate sum of transactions that took place in a month say February which will be represented by string '02' in SQLite, further more we want to group the sums or totals by day. Like how many transactions happened on day 01 how many on day 02 and so on. The query for the task is given below.
SELECT strftime('%d', trxDateTime) as valDay, SUM(trxAmount) as valTotalDay FROM trx_log WHERE strftime('%Y', trxDateTime)='2014' AND strftime('%m', trxDateTime) ='02' GROUP BY valDay
SQLite does not sport a separate date time data type, you must store the date time related data in one of two formats, first is the date string format which is:
Second is plain NUMERIC field type storing stuff in Unix timestamps. Be careful when you're calculating timestamps using Android or Desktop Java Date.getDate() function it's representing the timestamps in millisecond. You'll have to divide the figure by 1000 before performing any calculation on it.
Use the method SQLiteDatabase.rawQuery to execute these queries on your SQLite database.
SQLite is such a popular in memory database, it's being used on both Apple iOS and Google Android devices.
PS: The table structure screen shot is taken using SQLite Browser software.