Get count of records in data by groups

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 calculate the number of customers that each seller has. In this case:

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

This program reads text files with this data and does not connect to the database, so SQL is not an option.

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

Answer 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 

Answer 2

• Create a SalesStats class with string Name element and int ClientCount = 0
• Create a List<SalesStats>
• Open file
• Read each line and
  • Find a seller in the SalesStats OrElse collection SalesStats add a new Saleman using Name
  • add ClientCount for this seller
• Close file

The resulting list should have the amount of customers for each seller