Xamarin Forms - SQLITE Query

Question

I'm sorry it's pretty simple but I just couldn't get my head around it. I've checked the internet and search for answers but I couldn't find a specific one for my question or I just don't know how to look for it. I'm fairly new in Xamarin Forms and I'm stuck with this problem with using SQLite; I am able to get all the values from a column and display it to a ListView. Now, I want to display the sum of the items in the ListView but I couldn't get it at all. The database has two entries so far with 20 and 5 respectively to the column I want to get the sum of. Here's what I tried so far:

var getSum = conn.Query<tblAccount>("Select sum(ac_income) from tblAccount where ac_date between ? and ? and ac_incomeOrExpense =?", dpIncomeFrom.Date, dpIncomeTo.Date, "income").ToList();

//1st i tried - return a long string of column description

var getSum = conn.Table<tblAccount>().Where(u => u.ac_incomeOrExpense =="income").Sum(a => a.ac_income);

//this shows something of the same as the below codes but still it's not the correct result I was looking for

var getSum = conn.Table<tblAccount>().Sum(a => a.ac_income);
//for some reason this gives a result of 87 - don't know where it's getting that

So as obvious as it is, I'm still wrapping my head around how to call a query properly with Xamarin forms. If anyone could help or at least an example of how to call a query and displaying it properly, I'll be grateful. Thanks.

Your getting an 87, and it might be correct. Add this line and put a breakpoint on it: var zzz = conn.Table<tblAccount>();. Then execute the line and look at what is inside zzz. — dev1998
I added it and ran it and it showed it has a a value of: {SQLite.TableQuery<Model.tblAccount>} Unsure how its getting 87 and I'm looking on how to have it displayed 25 (20 + 5) which is the sum of the 2 rows respectively. — joppiealiw
Alternatively, I'm just looking on how to get the sum the total of the values in my listview. Those rows are being displayed in my listview. so row 1 = 20 row 2=5. I just want to get the sum of those and display it in a label. — joppiealiw
Were you able to drill down into the results and see the records? — dev1998
@dev1998 i was able to display it. Thanks. I posted the codes. thanks a lot! — joppiealiw

joppiealiw joppiealiw · Accepted Answer · 2018-01-08T20:34:27

I was able to figure it out. I was able to get the sum of the whole column (2 rows) and had it displayed 25.

Code:

var zzz = conn.Table<tblAccount>()
   .Where(p => p.ac_incomeOrExpense=="income");

lblTotalIncome.Text = "Total Income: " + zzz.Sum(p => p.ac_income);

Xamarin Forms - SQLITE Query

1 Answers