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

[Yafuhenk]

In the meantime I learnt that you can get the sum of a column in the B4XTable with

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]

General code to go over all matches or all over rows if there is no search query:

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]

That works perfect.

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

 

[Erel]

Post a screenshot so we will see what you are building.

 

[Yafuhenk]

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.

 

[Erel]

Looks very nice. You should push the labels to the right, or add padding.