How to: DISTINCT, SUM, COUNT the DataTable for a given XML?

Sometimes, you desire that you want something very quick. So you plan on using the Xml as data source and LINQ as a processor.

And then you desire to SELECT DISTINCT, and SUM, and COUNT on the DataTable;

So, for following,

Input:

       
          HR
          8.2
          3
       
       
          Marketing
          8.8
          3
       
       
          HR
          7.2
          4
       
       
          Admin
          8.9
          4
       
    

YOu want an output of the sort:

HR 7.7
Marketing 8.8
Admin 8.9

Following is the code to do not just that but alot more, using LINQ.

//Sample xml, taken from my answer@SO[http://stackoverflow.com/q/3870511/82449]
string xml = @"
       
          HR
          8.2
          3
       
       
          Marketing
          8.8
          3
       
       
          HR
          7.2
          4
       
       
          Admin
          8.9
          4
       
    
    ";
//Load into a reader
System.IO.StringReader readerXml = new System.IO.StringReader(xml);

//Prepare dataset
DataSet ds = new DataSet();

//Read xml reader
ds.ReadXml(readerXml);

DataTable dtTable = ds.Tables[0];

Use linq, the core of the post, to get average/count...

//Use linq to Average/Distinct rows.
var result = from theRow in dtTable.DataSet.Tables[0].AsEnumerable()
             group theRow by new
             {
                 theRow = theRow.Field("category")//Group by category.
             } into Group
             select new
             {
                 Row = Group.Key.theRow,
                 Count = Group.Count(),
                 Average = Group.Average(row => Decimal.Parse(row.Field("performance"))),
                 UniqueRows = (from p in Group
                               select p.Field("performance")).Distinct().Count()
             };

Output:

foreach (var res in result)
{
    MessageBox.Show(string.Format("Row:{0}, Total:{1}, Average Performance: {2}, Unique Records: {3}",
                    res.Row, res.Count, res.Average, res.UniqueRows));

}

Enjoy!