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
Add a comment
|
2 Answers
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]);
answered Apr 17, 2020 at 2:38
Luis Miguel Mantilla
1,7681 gold badge11 silver badges13 bronze badges
Comments
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 ...', ... )
