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



First, we have the SELECT keyword as usual. After that, I used SQLite strftime function along with the format string '%Y'. As a result, the SQLite query returns '2014' in the result set column named valYear, which is used in the query's WHERE clause and the GROUP BY clause of the query.



SQLLite Query Sum and Group By Month and Year

Suppose I calculate the SUM total of all the transactions which took place in all months of 2014 (or any given year, for that matter)and show those as a column in the query results. In this case, the SUM aggregate function of SQLite and a slightly different clause and GROUP BY will be used in our Sequel(SQL) query.
SELECT strftime('%m', trxDateTime) as valMonth, 
SUM(trxAmount) as valTotalMonth 
FROM trx_log 
WHERE strftime('%Y', trxDateTime)='2014' GROUP BY valMonth

A Database
Please note that the data set returned by this SQLLite against this SQL query will have two result set data columns valMonth and valTotalMonth; the column valMonth will have the numeric values from 01 to 12.
You can see that the database engine will filter the data first based on the criteria specified in the WHERE clause. Then it will apply the GROUP BY operation to put data in excellent groups based on the data table column name specified in the query.


SQLite 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 the string '02' in SQLite; furthermore, 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 distinct date-time data type(at the 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

The second is the primary NUMERIC field type storing stuff in Unix timestamps. Be careful when calculating timestamps using the Android or Desktop Java Date.getDate() method call. It represents the timestamps in milliseconds. So you'll have to divide the figure by 1000 before calculating it.

Use the method SQLiteDatabase.rawQuery to execute these queries on your SQLite database.

SQLite is a popular in-memory database used on Apple iOS and Google Android devices.


PS: The table structure screenshot is taken using SQLite Browser software.

Group of people in yellow t-shirts
A group of people

10 comments:

  1. That's ok, but, how can I list all months or all days even with zero value, like this:

    Jan Feb Mar
    0 5 0

    ...

    Thanks

    ReplyDelete
    Replies
    1. A Programmer'S Day: Group By Date Time, Day, Month, Year, Calculate Sum Function In Sqlite For App Developers >>>>> Download Now

      >>>>> 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

      Delete
  2. WITH month_table as (
    SELECT '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;

    ReplyDelete
  3. Dear Omar, I guess you've answered your question :)
    Otherwise, I didn't know how to do it. Thank you very much for your contribution.
    Naeem.

    ReplyDelete
  4. A Programmer'S Day: Group By Date Time, Day, Month, Year, Calculate Sum Function In Sqlite For App Developers >>>>> Download Now

    >>>>> 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

    ReplyDelete
  5. 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
  6. เว็บพนันออนไลน์ website that have all the betting in it to answer all the needed for the gambler

    ReplyDelete

Feel free to talk back...