Sqllite Strftime Function to Get Grouped Data by Months

Group by month in SQLite

it is always good while you group by MONTH it should check YEAR also

select SUM(transaction) as Price, 
       DATE_FORMAT(transDate, "%m-%Y") as 'month-year' 
       from transaction group by DATE_FORMAT(transDate, "%m-%Y");

FOR SQLITE

select SUM(transaction) as Price, 
       strftime("%m-%Y", transDate) as 'month-year' 
       from transaction group by strftime("%m-%Y", transDate);

How to group by a date column by month

Your biggest problem is that SQLite won't directly recognize your dates as dates.

CREATE TABLE YOURTABLE (DateColumn date);
INSERT INTO "YOURTABLE" VALUES('2012-01-01');
INSERT INTO "YOURTABLE" VALUES('2012-08-01 16:39:17.601455+0530');

If you try to use strftime() to get the month . . .

sqlite> select strftime('%m', DateColumn) from yourtable;
01

. . . it picks up the month from the first row, but not from the second.

If you can reformat your existing data as valid timestamps (as far a SQLite is concerned), you can use this relatively simple query to group by year and month. (You almost certainly don't want to group by month alone.)

select strftime('%Y-%m', DateColumn) yr_mon, count(*) num_dates 
from yourtable 
group by yr_mon;

If you can't do that, you'll need to do some string parsing. Here's the simplest expression of this idea.

select substr(DateColumn, 1, 7) yr_mon, count(*) num_dates 
from yourtable 
group by yr_mon;

But that might not quite work for you. Since you have timezone information, it's sure to change the month for some values. To get a fully general solution, I think you'll need to correct for timezone, extract the year and month, and so on. The simpler approach would be to look hard at this data, declare "I'm not interested in accounting for those edge cases", and use the simpler query immediately above.

GROUP BY month and year - SQLite

We can use SUBSTR() here to isolate the month and year, then aggregate:

SELECT SUBSTR(data, 4, 7) AS ym, SUM(quantidade) AS sum
FROM stock_tracking_Negociacao
WHERE mercado = 'Futuro'
GROUP BY 1;

Note that SQLite does not have a formal date type, but rather stores dates as strings.

SQLite Group by Month

You can use the strftime built-in function to extract the month from the stored text value and group by this.

A full list of the datetime functions can be found here http://sqlite.org/lang_datefunc.html

Select data based on month and year

Thanks for the answer provided by simolus3

 final asDate = FunctionCallExpression('date', [t.createdAt]);
 final year = FunctionCallExpression<String, StringType>(
      'strftime', [const Constant<String, StringType>('%Y'), asDate]);
 final month = FunctionCallExpression<String, StringType>(
      'strftime', [const Constant<String, StringType>('%m'), asDate]);

  return year.equals('2019') & month.equals('07');

Android : Calculate sum and group by month (SQLite)

I believe that a query based upon :-

SELECT sum(COLUMN_AMOUNT) AS Monthly_Total,substr(COLUMN_DATE_STRING,4) AS Month_and_Year
    FROM TABLE_BILLS 
    WHERE COLUMN_BILL_USER_ID = 1
  GROUP BY substr(COLUMN_DATE_STRING,4)
    ORDER BY substr(COLUMN_DATE_STRING,7,2)||substr(COLUMN_DATE_STRING,4,2)
;

• Note that other columns values would be arbritary results and as such cannot really be relied upon (fine if the data is always the same). Hence they have not been included.

Will produce the results that you want :-

e.g.

Using the following, to test the SQL :-

DROP TABLE IF EXISTS TABLE_BILLS;
CREATE TABLE IF NOT EXISTS TABLE_BILLS (
    COLUMN_BILL_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    COLUMN_BILL_USER_ID INTEGER,
    COLUMN_DESCRIPTION TEXT,
    COLUMN_AMOUNT INTEGER,
    COLUMN_DATE_STRING TEXT,
    COLUMN_COMPANY_NAME TEXT,
    COLUMN_CATEGORY TEXT)
    ;

-- Add the Testing data
INSERT INTO TABLE_BILLS (
    COLUMN_BILL_USER_ID, COLUMN_DESCRIPTION, COLUMN_AMOUNT, COLUMN_DATE_STRING, COLUMN_COMPANY_NAME,COLUMN_CATEGORY)
VALUES 
        (1,'blah',230,'04/03/19','cmpny','category')
        ,(1,'blah',500,'05/03/19','cmpny','category')
        ,(1,'blah',400,'04/04/19','cmpny','category')
        ,(1,'blah',600,'06/04/19','cmpny','category')
        ,(1,'blah',100,'04/03/19','cmpny','category')

        -- Extra data for another id to check exclusion
        ,(2,'blah',230,'04/03/19','cmpny','category')
        ,(2,'blah',500,'05/03/19','cmpny','category')
        ,(2,'blah',400,'04/04/19','cmpny','category')
        ,(2,'blah',600,'06/04/19','cmpny','category')
        ,(2,'blah',100,'04/03/19','cmpny','category')
;

SELECT sum(COLUMN_AMOUNT) AS Monthly_Total,substr(COLUMN_DATE_STRING,4) AS Month_and_Year
    FROM TABLE_BILLS 
    WHERE COLUMN_BILL_USER_ID = 1
  GROUP BY substr(COLUMN_DATE_STRING,4)
    ORDER BY substr(COLUMN_DATE_STRING,7,2)||substr(COLUMN_DATE_STRING,4,2)
;

Results id :-

The above can then be converted for use by the SQLiteDatabase query method. So your method could be something like :-

public ArrayList<Bills> getDateByUserID(int userID) {
    SQLiteDatabase db = this.getReadableDatabase();
    String tmpcol_monthly_total = "Monthly_Total";
    String tmpcol_month_year = "Month_and_Year";
    String[] columns = new String[]{
            "sum(" + COLUMN_AMOUNT + ") AS " + tmpcol_monthly_total,
            "substr(" + COLUMN_DATE_STRING + ",4) AS " + tmpcol_month_year
    };
    String whereclause = COLUMN_BILL_USER_ID + "=?";
    String[] whereargs = new String[]{String.valueOf(userID)};
    String groupbyclause = "substr(" + COLUMN_DATE_STRING + ",4)";
    String orderbyclause = "substr(" + COLUMN_DATE_STRING + ",7,2)||substr(" + COLUMN_DATE_STRING + ",4,2)";
    ArrayList<Bills> listBillsDates = new ArrayList<Bills>();

    Cursor cursor = db.query(TABLE_BILLS, columns, whereclause,
            whereargs, groupbyclause, null, orderbyclause, null);
    if (cursor.moveToFirst()) {
        do {
            Bills bills = new Bills();
            bills.setAmount(cursor.getInt(cursor.getColumnIndex(tmpcol_monthly_total)));
            bills.setDateString(cursor.getString(cursor.getColumnIndex(tmpcol_month_year))); //<<<<<<<<<< NOTE data is MM/YY (otherwise which date to use? considering result will be arbrirtaryy)
            // Adding record to list
            listBillsDates.add(bills);
        } while (cursor.moveToNext());
    }
    cursor.close();
    db.close();

    // return category list
    return listBillsDates;
}

• The above has been tested and run and using the following code :-

  ArrayList<Bills> myMonthlyTotals = mDBHelper.getDateByUserID(1);
  Log.d("BILLSCOUNT","The number of bills extracted was " + String.valueOf(myMonthlyTotals.size()));
  for (Bills b: myMonthlyTotals) {
      Log.d("MONTHYLTOTAL","Monthly total for " + b.getDateString() + " was " + String.valueOf(b.getAmount()));
  
  }
  

• In an activity, resulted in the following in the log

:-

04-14 11:58:25.876 16653-16653/? D/BILLSCOUNT: The number of bills extracted was 2
04-14 11:58:25.877 16653-16653/? D/MONTHYLTOTAL: Monthly total for 03/19 was 830
04-14 11:58:25.877 16653-16653/? D/MONTHYLTOTAL: Monthly total for 04/19 was 1000

• Please consider the comments in regard to values from non-aggreagted columns be arbitrary values. As per :-

• Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Or, if the dataset contains zero rows, then each non-aggregate expression is evaluated against a row consisting entirely of NULL values. SELECT - 3. Generation of the set of result rows.

As per the comments, using recognised date formats can make the underlying SQL simpler and likely more efficient.

How to group dates in Quarters in SQLite

The format of your dates is not YYYY-MM-DD which is the only valid date format for SQLite.

So if you want to extract the month of a date, any date function that SQLite supports will fail.

You must use the string function SUBSTR() to extract the month and then other functions like NULLIF() and COALESCE() to adjust the quarter to your requirement.

Assuming that the format of your dates is DD/MM/YYYY:

SELECT Close_Date,
       'Q' || COALESCE(NULLIF((SUBSTR(Close_Date, 4, 2) - 1) / 3, 0), 4) AS Quarter
FROM tablename

If the format is MM/DD/YYYY then change SUBSTR(Close_Date, 4, 2) to SUBSTR(Close_Date, 1, 2) or just Close_Date because SQLite will implicitly convert the date to a number which will be the starting digits of the date.

See the demo.

Results:

> Close_Date | Quarter
> :--------- | :------
> 01/04/2019 | Q1     
> 01/05/2019 | Q1     
> 01/10/2019 | Q3     
> 01/09/2019 | Q2     
> 01/06/2019 | Q1     
> 01/09/2019 | Q2     
> 01/04/2019 | Q1     
> 01/07/2019 | Q2 

---

Related Topics