Try this:
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn("Fruit Name", typeof(string)),
new DataColumn("Is Rotten", typeof(string))
});
dt.Rows.Add(new object[]{"Apple", "yes"});
dt.Rows.Add(new object[]{"Apple", "no"});
dt.Rows.Add(new object[]{"Apple", "no"});
dt.Rows.Add(new object[]{"Apple", "no"});
dt.Rows.Add(new object[]{"Orange", "no"});
dt.Rows.Add(new object[]{"Orange", "no"});
dt.Rows.Add(new object[]{"Orange", "no"});
dt.Rows.Add(new object[]{"Orange", "no"});
dt.Rows.Add(new object[]{"Orange", "yes"});
dt.Rows.Add(new object[]{"Orange", "yes"});
dt.Rows.Add(new object[]{"Orange", "no"});
var result = dt.AsEnumerable()
.GroupBy(x=>x.Field<string>("Fruit Name"))
.Select(grp=> new
{
FruitName = grp.Key,
NoOfRotten = grp.Count(x=>x.Field<string>("Is Rotten")=="yes"),
TotalNo = grp.Count()
})
.ToList();
Console.WriteLine("{0}\t|\t{1}\t|\t{2}", "FruitName", "NoOfRotten", "TotalNo");
foreach(var r in result)
{
Console.WriteLine("{0}\t|\t{1}\t|\t{2}", r.FruitName, r.NoOfRotten, r.TotalNo);
}
Result:
FruitName | NoOfRotten | TotalNo
Apple | 1 | 4
Orange | 2 | 7