B4J Question SOLVED B4XTable How to get the sum of all selected (searched) values in a column

Yafuhenk

Active Member
Licensed User
In the meantime I learnt that you can get the sum of a column in the B4XTable with
B4X:
Dim total As Double = B4XTable1.sql1.ExecQuerySingleResult($"SELECT sum(${Column.SQLID}) FROM data"$)
But what if the user searched for a certain text. Do I have to work with the WHERE statement in the query or is there something like "FROM FilteredData".
 

Erel

Administrator
Staff member
Licensed User
General code to go over all matches or all over rows if there is no search query:
B4X:
Dim o() As Object = B4XTable1.BuildQuery(False) 'no page limit
Dim total As Double
Dim rs As ResultSet = B4XTable1.sql1.ExecQuery2(o(0), o(1))
Do While rs.NextRow
    total = total + rs.GetDouble(numberColumn.SQLID) 'change column as needed
Loop
rs.Close
Log(total)
 

Yafuhenk

Active Member
Licensed User
That works perfect.
B4X:
Sub SetColumnSum(columnID As String)
    Dim Column As B4XTableColumn = B4XTable1.GetColumn(columnID)
    Dim o() As Object = B4XTable1.BuildQuery(False) 'no page limit
    Dim total As Double
    Dim rs As ResultSet = B4XTable1.sql1.ExecQuery2(o(0), o(1))
    Do While rs.NextRow
        total = total + rs.GetDouble(Column.SQLID)
    Loop
    rs.Close
    Dim pnl As B4XView = Column.CellsLayouts.Get(0)
    Dim lbl As B4XView = pnl.GetView(0)
    'add totals to column header
    lbl.Text = columnID & CRLF & "∑ " & Formatter.Format(total)
End Sub
 

Yafuhenk

Active Member
Licensed User
Sorry for my late reply but I had to create some dummy data first.
In the end the software will import json files (via OData-queries) from a SAP server and store the result temporary in an in-memory database.
Still figuring out how to secure the data the best way.
Here are some pictures.

0.PNG1.PNG2.PNG3.PNG
 
Top