Help with dictionary for counting

I am reading values a SQLITE database and want to create a dictionary based on the values in a specific column. The dictionary would have a key of the text string from the database, and the value would be a count of the number of times that the string has appeared. Problem is, I don’t know what the values are in the DB until I run the select statement.

In short, how can I build a count of the number of times each string appears in a column, using a dictionary?

As an example, the below table would produce the dictionary underneath:

Cities
London
Manchester
London
Manchester
Birmingham
Reading
Reading
Reading
Londn
Newcastle
Newcastle

|London|2|
|Manchester|2|
|Birmingham|1|
|Reading|3|
|Londn|1|
|Newcastle:2|

I was making this way too hard, i’ll post the answer in case anyone else tries the same approach as I was. :man_facepalming:t2:

Why try and code it when you can have the database do it for you?

rs = db.SelectSQL("SELECT Cities, COUNT(*) FROM locations GROUP BY Cities")

3 Likes

That is the best way, but to answer your original question anyway, something like this:

d.Value( key ) = d.Lookup( key, 0 ).IntegerValue + 1
2 Likes