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

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:

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]

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:

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:

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

RBS

 

[Claudio Oliveira]

Hi RBS!

Looks like native SQL could do this for you:

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

 

[RB Smissaert]

Hi RBS!

Looks like native SQL could do this for you:

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

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

RBS

 

[Claudio Oliveira]

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

I see... In this case, your approach looks pretty fine.

 

[Erel]

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

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]

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

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