Infost Infost - 2 months ago 7
C# Question

Count number of occurences for each distict item in a datacolumn

I am using C# ConoleApplication.

I have ODBC DSN connection that i bring into Datatable.
Column One is "Account#".

Example:

Account#------------|

AA1-------------------|

AA1-------------------|

AA2-------------------|

AA3-------------------|

AA1-------------------|

AA4-------------------|

AA2-------------------|

I am trying to work out how to give the following result.
I would think it would be totaled into a new datatable but i cant make any code work that I have tried.

Account#------------| Occurences

AA1-------------------|3

AA2-------------------|2

AA3-------------------|1

AA4-------------------|1

Please how can i do this?

Answer

You can use Linq-To-DataTable and Enumerable.GroupBy:

var accountGroups = table.AsEnumerable()
    .GroupBy(row => row.Field<String>("Account#"))
    .Select(grp => new { Account = grp.Key, Count = grp.Count() });

If you need a new DataTable:

var tblAccCounts = new DataTable();
tblAccCounts.Columns.Add("Account#");
tblAccCounts.Columns.Add("Count", typeof(int));

foreach(var grp in accountGroups)
    tblAccCounts.Rows.Add(grp.Account, grp.Count);
Comments