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?
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
SalesStats
class with a string Name
and an int ClientCount = 0
memberList<SalesStats>
SalesStats
collection OrElse add new Saleman with Name
The resulting List should have the sum of clients for each salesman
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With