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.
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 Table used in example of GROUP BY date time, month, year, and SUM funciton |
SQLLite Query Sum and Group By Year
How to calculate the 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
First we have the SELECT keyword as usual. After that I have used SQLite strftime function along with format string '%Y' . As a result, the SQLite query returns '2014' in result set column named valYear which is used in the WHERE clause of the query and GROUP BY clause of the query as well.
SQLLite Query Sum and Group By Month and Year
Now suppose I want to calculate the SUM total of all the transactions which took place in all months of 2014 (or any other given year for that matter)and show those as a column in the query results. The SUM aggregate function of SQLite and a slightly different where clause and GROUP BY will be used in our Sequel(SQL) query in this case.SELECT strftime('%m', trxDateTime) as valMonth, SUM(trxAmount) as valTotalMonth FROM trx_log WHERE strftime('%Y', trxDateTime)='2014' GROUP BY valMonth
![]() |
A Database |
You can see that the database engine is going to filter the data first based on the criteria specified in the WHERE clause and then it is going to apply the GROUP BY operation to put data in nice groups based on the data table column name specified in the query.
SQLLite Query SUM and GROUP BY Day, Month, and Year
Next up we got a query to calculate the 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. This will show 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(on time of this writing), you must store the date time related data in one of two formats, first is the date string format which is:
YYYY-MM-dd HH:mm:ss.SS
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() method call. Its 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.
![]() |
A group of people |
That's ok, but, how can I list all months or all days even with zero value, like this:
ReplyDeleteJan Feb Mar
0 5 0
...
Thanks
A Programmer'S Day: Group By Date Time, Day, Month, Year, Calculate Sum Function In Sqlite For App Developers >>>>> Download Now
Delete>>>>> Download Full
A Programmer'S Day: Group By Date Time, Day, Month, Year, Calculate Sum Function In Sqlite For App Developers >>>>> Download LINK
>>>>> Download Now
A Programmer'S Day: Group By Date Time, Day, Month, Year, Calculate Sum Function In Sqlite For App Developers >>>>> Download Full
>>>>> Download LINK ye
WITH month_table as (
ReplyDeleteSELECT '01' as month,'Jan' UNION ALL
SELECT '02' as month,'Feb' UNION ALL
SELECT '03' as month,'Mar' UNION ALL
SELECT '04' as month,'Apr' UNION ALL
SELECT '05' as month,'May' UNION ALL
SELECT '06' as month,'Jun' UNION ALL
SELECT '07' as month,'Jul' UNION ALL
SELECT '08' as month,'Aug' UNION ALL
SELECT '09' as month,'Sep' UNION ALL
SELECT '10' as month,'Oct' UNION ALL
SELECT '11' as month,'Nov' UNION ALL
SELECT '12' as month,'Dec')
SELECT t.month,sum(f.value) FROM month_table t,finance f where user_id = 4 and strftime('%Y', f.dt_lancamento) = '2015' and strftime('%m', f.dt_lancamento) = t.month group by t.month;
Dear Omar, I guess you've answered your question :)
ReplyDeleteOtherwise, I didn't know how to do it. Thank you very much for your contribution.
Naeem.
good job
ReplyDeleteEminem net worth 2021: Eminem is a multi-platinum selling American rapper, producer and actor who has a net worth of $230 million. He is consistently one of the highest-paid entertainers in the world.
ReplyDeleteสล็อตออนไลน์ Made for entertainment.
ReplyDeleteA Programmer'S Day: Group By Date Time, Day, Month, Year, Calculate Sum Function In Sqlite For App Developers >>>>> Download Now
ReplyDelete>>>>> Download Full
A Programmer'S Day: Group By Date Time, Day, Month, Year, Calculate Sum Function In Sqlite For App Developers >>>>> Download LINK
>>>>> Download Now
A Programmer'S Day: Group By Date Time, Day, Month, Year, Calculate Sum Function In Sqlite For App Developers >>>>> Download Full
>>>>> Download LINK
Thank you so much for the post you do. I like your post and all you share with us is up to date and quite informative. ทางเข้าเล่น 789betting
ReplyDeleteราคาบอลไหล The best online football betting websites
ReplyDeleteไพ่แคงออนไลน์ Card games have a playing system that is fun and easy to understand. Show the cards and compare the scores to finish the game.
ReplyDeletenaza 24 new money making website
ReplyDeleteเว็บ naza easy to play, get real money
ReplyDeleteเกม 123 best online gambling games Click Now
ReplyDeleteMaria The number 1 best website to make Money
ReplyDeleteยอดเทิร์นโอเวอร์Best Casino Sites Click Now
ReplyDeleteซื้อหวยหุ้น We provide best services to all class of clients.
ReplyDeletewwbet Lottery website only.
ReplyDelete