Using SQLite SUM function with Xojo

Databases
1

I’d like to grab the sum of a column in a query and use it to update the value in a field on the screen. I tried something like this, but I have a feeling I’m not integrating the SUM value into Xojo property:

[code]Dim sql As String
Dim rs As RecordSet
sql = “SELECT SUM(Widgets) FROM Purchases WHERE Year = yyyy AND Month = mm”
rs = db.SQLSelect(sql)

TotalWidgetTextField.Text = rs.Field(“SUM”).StringValue[/code]

2

you should alias the result

Dim sql As String
Dim rs As RecordSet
sql = "SELECT SUM(Widgets) AS mysum FROM Purchases WHERE Year = yyyy AND Month = mm"
rs = db.SQLSelect(sql)

TotalWidgetTextField.Text = rs.Field("mysum").StringValue
3

While I personally prefer the alias approach Dave already gave, there is also the possibility to ask for the first field returned:

TotalWidgetTextField.Text = rs.IdxField(1).StringValue

The result set fields are numbered from one, not zero.