Get count of records in datatable by groupings

Question

I have a DataTable with the following data:

Salesman---ClientID
Bob--------1
Bob--------2
Bob--------3
Tom--------4
Joe--------5
Joe--------6
Tim--------7
Tim--------8

From this, I would like to get a count of how many clients each salesman has. In this case:

Salesman---CountOfClients
Bob--------3
Tom--------1
Joe--------2
Tim--------2

This program is reading in text files with this data and is not connected to a database, so SQL is not an option.

Using C#, how can I accomplish the desired results?

Solution 1

If you can use LINQ, then the groupby clause will do the aggregation for you.

// this is your datatable
DataTable table = new DataTable();
table.Columns.Add("Salesman", typeof(string));
table.Columns.Add("ClientID", typeof(int));

//insert your data
table.Rows.Add("Bob", 1);
table.Rows.Add("Bob", 2);
table.Rows.Add("Bob", 3);
table.Rows.Add("Tom", 4);
table.Rows.Add("Joe", 5);
table.Rows.Add("Joe", 6);
table.Rows.Add("Tim", 7);
table.Rows.Add("Tim", 8);

// query with LINQ 
var query = from row in table.AsEnumerable()
            group row by row.Field<string>("Salesman") into sales
            orderby sales.Key
            select new
            {
                Name = sales.Key,
                CountOfClients = sales.Count()
            };

// print result
foreach (var salesman in query)
{
    Console.WriteLine("{0}\t{1}", salesman.Name, salesman.CountOfClients);
}

Output:

Bob    3
Joe    2
Tim    2
Tom    1

Solution 2

1. Create a SalesStats class with a string Name and an int ClientCount = 0 member
2. Create a List<SalesStats>
3. Open the file
4. Read each line and
   • Find salesman in SalesStats collection OrElse add new Saleman with Name
   • increment the ClientCount for that salesman
5. Close file

The resulting List should have the sum of clients for each salesman

Solution 3

Old question I know but it came up when I was looking. I personally found great results with...

Dictionary<object, Int32> myGroupings = myDataTable.AsEnumerable().GroupBy(p => p.Field<object>("FieldName")).ToDictionary(p => p.Key, p => p.Count());

And just as a side note.. to then find out what the largest count number was...

Int32 intDenominator = myGroupings.Values.Max();