How to get data month wise sqflite in Flutter?

Question

Image for how does data look I am making an expense tracker app using flutter, I want to show the sum of expenses done by the user every month for example:

Month              Amount Spended
January              2000
February             1600

Database columns:
"CREATE TABLE $TABLE_EXPENSES ("
      "$COLUMN_ID INTEGER PRIMARY KEY,"
      "$COLUMN_NAME TEXT,"
      "$COLUMN_AMOUNT TEXT,"
      "$COLUMN_UNNECESSARYEXPENSES INTEGER,"
      "$COLUMN_CATEGORY TEXT,"
      "$COLUMN_DATETIME TEXT"
      ")",

I am using sqflite to create a database and I am storing data as text. I want to retrieve expenses of every day in a month and then sum up the expenses of every day and show it in ListTile

Edit:

Query: 'SELECT * SUM(COLUMN_AMOUNT) FROM TABLE_EXPENSES WHERE COLUMN_DATETIME >= ? AND COLUMN_DATETIME <= ?'

This is the error that I am getting (for query):

E/SQLiteLog(10318): (1) near "SELECT": syntax error
E/flutter (10318): [ERROR:flutter/lib/ui/ui_dart_state.cc(177)] Unhandled Exception: DatabaseException(near "SELECT": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM expenses WHERE SELECT * SUM(COLUMN_AMOUNT) FROM TABLE_EXPENSES WHERE COLUMN_DATETIME >= 2019 AND COLUMN_DATETIME <= 1989) sql 'SELECT * FROM expenses WHERE SELECT * SUM(COLUMN_AMOUNT) FROM TABLE_EXPENSES WHERE COLUMN_DATETIME >= ? AND COLUMN_DATETIME <= ?' args [2019, 1989]}

Function which I am currently using:

Future getDataJan() async{
final db = await database;
sumJan= db.rawQuery(
    'SELECT SUM(AMOUNT) FROM EXPENSES WHERE DATETIME >= ? AND DATETIME <= ?',
    [2021-01-01, 2021-01-31] ).then(Sqflite.firstIntValue);
finalJan=sumJan.toString();

}

Image: errorImage

Thanks for your replies.

Solution

You can operate with a date with format like yyyy-MM-dd HH:mm:SS and create requests to database for search entities between month.

Date format example:

DateFormat('yyyy-MM-dd HH:mm:SS').format(date)

Query example:

return database.query(
  tableName,
  where: 'date >= ? and date <= ?',
  whereArgs: [from, to],
)
.then((data) => data.map(_fromMap).toList());

Variables in example:

1. from - first day of month (2020-10-01);
2. to - last day of month (2020-10-31);
3. ? in where - takes values from whereArgs (first ? - from, second - to);

Note: in comments describes that you should use SQFLite tooling for creating requests and do not use raw queries (for performance reasons).

Select example

final result = await database.rawQuery(
  'select * sum(COLUMN_AMOUNT) from TABLE_EXPENSES where COLUMN_DATETIME >= ? and COLUMN_DATETIME <= ?',
  [from, to],
).then(Sqflite.firstIntValue);

Comments

[Ahx]

Where is the MCVE of the question? Please include it to the question. Also make sure to read How do I ask good question?

[forpas]

Post sample data of your table.

[Sanchay Kasturey]

I've added it hanks

[Sanchay Kasturey]

can i use group by month over here and if yes hw can I use it?

[fartem]

You can try to usegroupBy. Something like group by date,

[Randal Schwartz]

Please don't interpolate into a where clause. Use placeholders. See bobby-tables.com for details.

[fartem]

@RandalSchwartz Thanks, I am complete the answer.

[Sanchay Kasturey]

I am using SQFlite only, thanks for your updated answer and reply I will try this one..

[Sanchay Kasturey]

Future getDataJan() async{ final db = await database; sumJan= db.query(TABLE_EXPENSES, where: 'SELECT SUM(COLUMN_AMOUNT) FROM TABLE_EXPENSES WHERE COLUMN_DATETIME >= ? AND COLUMN_DATETIME <= ?', whereArgs:[2021-01-01, 2021-01-31] ); } can I use it like this as I need sum as well?

[Sanchay Kasturey]

yeah i am getting one ::[ERROR:flutter/lib/ui/ui_dart_state.cc(177)] Unhandled Exception: DatabaseException(near "SELECT": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM expenses WHERE SELECT * SUM(COLUMN_AMOUNT) FROM TABLE_EXPENSES WHERE COLUMN_DATETIME >= 2019 AND COLUMN_DATETIME <= 1989) sql 'SELECT * FROM expenses WHERE SELECT * SUM(COLUMN_AMOUNT) FROM TABLE_EXPENSES WHERE COLUMN_DATETIME >= ? AND COLUMN_DATETIME <= ?' args [2019, 1989]}

[fartem]

Please verify your SQL syntax.

[Sanchay Kasturey]

I don't know where am i wrong can you please help me??

[Sanchay Kasturey]

SELECT SUM(COLUMN_AMOUNT) FROM TABLE_EXPENSES WHERE COLUMN_DATETIME >= ? AND COLUMN_DATETIME <= ?', whereArgs:[2021-01-01, 2021-01-31] here it is

[fartem]

And you get error in SQL query or on the Flutter side?

[Sanchay Kasturey]

I am getting error on sql query can you please help me?

[fartem]

Yes, can you add error message to the question?

[Sanchay Kasturey]

I have added the query as well as the error I am getting

[Sanchay Kasturey]

but i want to store the sum of that amount in a variable, how can I do that?

[fartem]

You can do this by call count function from sqflite and extract a value by Sqflite.firstIntValue method.

[Sanchay Kasturey]

okay but then i would need to do the same for every month how would I differentiate it , I am not getting it please explain, currently I am making a function and then calling its variable :::::: 'Future getDataJan() async{ final db = await database; sumJan= db.query(TABLE_EXPENSES, where: 'SELECT * SUM(COLUMN_AMOUNT) FROM TABLE_EXPENSES WHERE COLUMN_DATETIME >= ? AND COLUMN_DATETIME <= ?', whereArgs:[2021-01-01, 2021-01-31] ); finalJan=sumJan.toString(); }'

[fartem]

Can you add your function to the question?

[Sanchay Kasturey]

I have added that

[fartem]

You need to call raw query from sqflite, without where statement.

[Sanchay Kasturey]

can you please provide an example to me? It would be really helpful

[Sanchay Kasturey]

I tried what you said but now I am getting an error the image for same has been posted in "errorImage"

[fartem]

You need to extract value with await. Database get result as Future.

[Sanchay Kasturey]

but i am doing it, I have updated my function (in question) can you please check it and tell me ?

[fartem]

You need to call function by this way: sumJan = await db.rawQuery.

[fartem]

Delete this line then(Sqflite.firstIntValue) and add this then((result) => result['sum('COLUMN_AMOUNT')']);

[Sanchay Kasturey]

No it is showing an error when I type result, error "Undefined name 'result' ".

[Sanchay Kasturey]

I guess the problem is in my function it is mentioned under "Function i am using" I am trying to get data in sumJan=query* then I am parsing it to string finalJan and then I am calling finalJan in my main file where I need to display the sum, I guess somewhere here I am doing wrong I guess the way of assigning in sumJan is wrong please refer to the question for the code of the same.

[fartem]

You can extract your database from the app's folder and test it with SQLite Browser.

[Sanchay Kasturey]

Okay i will try that