How to query a SQL Lite database using flutter sqflite

Question

I have setup a database following this answer: How to do a database query with SQFlite in Flutter but I want to get the sum of all the items in a column for the database I'm creating and then display that number on a TextLabel. I've looked but not able to find any information on where I could find a solution to this

Answer 1

SQFlite just like SQLite has Aggregate Functions and to execute a sum function you can do it as a rawQuery as follows:

Future<int> sumItems() async {
    final sum = await db.rawQuery("SELECT sum(id_word) as sum FROM Word");
    //print(sum[0]["sum"]);
    return sum[0]["sum"];
  }

To use the result of this function in your Text widget, you just have to use a FutureBuilder.

Additionally if you want to use arguments like a WHERE sentence or a Group By sentence, just add it an pass the arguments as an array.

    final sum = await db.rawQuery("SELECT sum(id_word) as sum FROM Word WHERE id_word = ?", [5]);

Answer 2

Refering to the same answer, you can do something like below :

List<Map> result = await db.query(DatabaseHelper.table);

int total = 0;    
// print the results
result.forEach((row) => total = total + row.price);

Or with the raw query, it's like this ;

Future calculateTotal() async {
  var dbClient = await db;
  var result = await dbClient.rawQuery("select sum(price) as Total from my_table");
}

int _total;

void _calcTotal() async{
  var total = (await db.calculateTotal())[0]['Total'];
  print(total);
  setState(() => _total = total)
}

@override
Widget build(BuildContext context) {
  ...  
  Text(_total != null ? _total : 'waiting ...', ... )