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

RB Smissaert

Well-Known Member
Licensed User
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
 

RB Smissaert

Well-Known Member
Licensed User
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
Sorry, forgot to post the CInt function:

B4X:
Sub CInt(o As Object) As Int
 Return Floor(o)
End Sub
RBS
 

Claudio Oliveira

Active Member
Licensed User
Hi RBS!

Looks like native SQL could do this for you:

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

Erel

Administrator
Staff member
Licensed User
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
 

RB Smissaert

Well-Known Member
Licensed User
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
Thanks, that is nicer indeed.
Wasn't aware of oMap.GetDefault.

RBS
 
Top