Seems, you have to group by two fields:
groupId
and
Department
.
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]{
new DataColumn("TableBId", typeof(int)),
new DataColumn("staff", typeof(string)),
new DataColumn("groupId", typeof(int)),
new DataColumn("division", typeof(int)),
new DataColumn("Department", typeof(int)),
new DataColumn("unitBranch", typeof(int))
});
dt.Rows.Add(new object[]{1, "ali", 1, 1, 1, 1});
dt.Rows.Add(new object[]{2, "Mohammad", 2, 1, 1, 1});
dt.Rows.Add(new object[]{3, "ahmad", 1, 1, 3, 1});
dt.Rows.Add(new object[]{4, "omar", 1, 1, 3, 1});
dt.Rows.Add(new object[]{5, "othman", 1, 1, 1, 1});
dt.Rows.Add(new object[]{6, "basam", 1, 1, 3, 1});
dt.Rows.Add(new object[]{7, "saaed", 3, 1, 3, 1});
var data = dt.AsEnumerable()
.GroupBy(x=>new {groupId = x.Field<int>("groupId"), Department = x.Field<int>("Department")})
.Select(grp=> new
{
groupId = grp.Key.groupId,
division = grp.Select(y=>y.Field<int>("division")).First(),
Department = grp.Key.Department,
unitBranch = grp.Select(y=>y.Field<int>("unitBranch")).First(),
Cnt = grp.Count()
});
Result:
groupId division Dep... unitBranch Cnt
1 1 1 1 2
2 1 1 2 1
1 1 3 1 3
3 1 3 3 1