Using SQLite SUM function with Xojo

  1. 2 weeks ago

    Ralph A

    Oct 11 Santa Monica, California

    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:

    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

    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
  2. Dave S

    Oct 11 Answer San Diego, California USA

    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. Douglas H

    Oct 11 Pre-Release Testers, Xojo Pro

    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.

or Sign Up to reply!