Android Code Snippet Group and count Recordset (or list or array etc.)

RB Smissaert said:

Needed a fast way to take a recordset and group and count a specified column, so that is get the unique items of that column and count the occurrences of all those unique items.

This the best I could come up with:

B4X:

Sub GroupAndCountRS(RS1 As ResultSet, iColumn As Int) As Map
 Dim i As Int
 Dim oMap As Map
 Dim strKey As String
 oMap.Initialize
 For i = 0 To RS1.RowCount - 1
  RS1.Position = i
  strKey = RS1.GetString2(iColumn)
  If oMap.ContainsKey(strKey) Then
   oMap.Put(strKey, CInt(oMap.Get(strKey)) + 1)
  Else
   oMap.Put(strKey, 1)
  End If
 Next
 Return oMap
End Sub

It is very fast indeed. A recordset with a column with largish (10 to 2000 chars) and about 400000 rows produces a map with 60000 values and counts in about 700 milli-seconds.


RBS

Click to expand...

Sub CInt(o As Object) As Int
 Return Floor(o)
End Sub

Claudio Oliveira said:

Hi RBS!

Looks like native SQL could do this for you:

B4X:

SELECT <column name>, COUNT(<column name>) GROUP BY <column name>

Click to expand...

RB Smissaert said:

Yes, sure, but I have to do it with a Recordset.

Click to expand...

Erel said:

1. A bit more elegant.
2. Cross platform (you are using B4A specific Cursor way of iterating over the results).

B4X:

Sub GroupAndCountRS(RS1 As ResultSet, iColumn As Int) As Map
   Dim oMap As Map
   oMap.Initialize
   Do While RS1.NextRow
       Dim strKey As String = RS1.GetString2(iColumn)
       oMap.Put(strKey, oMap.GetDefault(strKey, 0) + 1)
   Loop
   RS1.Close 'don't forget to close it
   Return oMap
End Sub

Click to expand...