none
Multiple LINQ aggregates RRS feed

  • Question

  • Hi.  I can't understand why I'm having so much difficulty googling an answer to this question.  Using C# I have a DataTable and I want to create a new DataTable with aggregate values using LINQ.  In SQL my query would be . . .

    select sum(A), max(B), avg(C)
    from TBL
    group by D

    Could somebody please tell me the best way to do this in LINQ?

    Many thanks,

    Michael
    Thursday, March 12, 2009 10:18 AM

Answers

  • A more complete example that doesn't need CopyToDataTable from an IEnumerable

    DataTable table = new DataTable("table");  
    table.Columns.Add("A", typeof(double));  
    table.Columns.Add("B", typeof(double));  
    table.Columns.Add("C", typeof(double));  
    table.Columns.Add("D", typeof(string));  
     
    table.Rows.Add(1.0, 1.0, 1.0, "1");  
    table.Rows.Add(2.0, 2.0, 2.0, "1");  
    table.Rows.Add(3.0, 3.0, 3.0, "1");  
     
    table.Rows.Add(10.0, 10.0, 10.0, "2");  
    table.Rows.Add(20.0, 20.0, 20.0, "2");  
    table.Rows.Add(30.0, 30.0, 30.0, "2");  
     
    DataTable target = new DataTable("target");  
    target.Columns.Add("D", typeof(string));  
    target.Columns.Add("SumA", typeof(double));  
    target.Columns.Add("MaxB", typeof(double));  
    target.Columns.Add("AvgC", typeof(double));  
     
    Func<DataTable, IGrouping<string, DataRow>, DataRow> newrow = delegate(DataTable targetTable, IGrouping<string, DataRow> group)  
    {  
        DataRow dst = targetTable.NewRow();  
        dst["D"] = group.Key;  
        dst["SumA"] = group.Sum(row => row.Field<double>("A"));  
        dst["MaxB"] = group.Max(row => row.Field<double>("B"));  
        dst["AvgC"] = group.Average(row => row.Field<double>("C"));  
        return dst;  // you need to either 'targetTable.Rows.Add(dst)' or 'dst.CancelEdit();'  
    };  
     
    var enumer =  
        from tblRow in table.AsEnumerable()  
        group tblRow by tblRow.Field<string>("D") into rows  
        select newrow(target, rows);  
     
    foreach (var val in enumer)  
    {  
        target.Rows.Add(val);  
        Console.WriteLine("D={0}, Sum={1}, Max={2}, Avg={3}", val.ItemArray);  

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to mark the replies as answers if they help.
    Thursday, March 12, 2009 4:58 PM
    Moderator

All replies

  • Thanks Zahid.

    The element I was missing was lambda expressions.  The answer, in case anybody suffers the same confusion is something like this . . .

    var enumer =  
        from tblRow in tbl.AsEnumerable()  
        group tblRow by Convert.ToString(tblRow["D"]) into rows  
        select new 
        {  
            D = rows.Key,  
            SumA = rows.Sum(row => Convert.ToDouble(row["A"])),  
            MaxB = rows.Max(row => Convert.ToDouble(row["B"])),  
            AvgC = rows.Average(row => Convert.ToDouble(row["C"]))  
        }  
     
     
    Then use an implementation of CopyToDataTable<>.  I hope I've written that correctly - the code I'm actually using is more complicated than that.

    Michael.
    Thursday, March 12, 2009 11:49 AM
  • A more complete example that doesn't need CopyToDataTable from an IEnumerable

    DataTable table = new DataTable("table");  
    table.Columns.Add("A", typeof(double));  
    table.Columns.Add("B", typeof(double));  
    table.Columns.Add("C", typeof(double));  
    table.Columns.Add("D", typeof(string));  
     
    table.Rows.Add(1.0, 1.0, 1.0, "1");  
    table.Rows.Add(2.0, 2.0, 2.0, "1");  
    table.Rows.Add(3.0, 3.0, 3.0, "1");  
     
    table.Rows.Add(10.0, 10.0, 10.0, "2");  
    table.Rows.Add(20.0, 20.0, 20.0, "2");  
    table.Rows.Add(30.0, 30.0, 30.0, "2");  
     
    DataTable target = new DataTable("target");  
    target.Columns.Add("D", typeof(string));  
    target.Columns.Add("SumA", typeof(double));  
    target.Columns.Add("MaxB", typeof(double));  
    target.Columns.Add("AvgC", typeof(double));  
     
    Func<DataTable, IGrouping<string, DataRow>, DataRow> newrow = delegate(DataTable targetTable, IGrouping<string, DataRow> group)  
    {  
        DataRow dst = targetTable.NewRow();  
        dst["D"] = group.Key;  
        dst["SumA"] = group.Sum(row => row.Field<double>("A"));  
        dst["MaxB"] = group.Max(row => row.Field<double>("B"));  
        dst["AvgC"] = group.Average(row => row.Field<double>("C"));  
        return dst;  // you need to either 'targetTable.Rows.Add(dst)' or 'dst.CancelEdit();'  
    };  
     
    var enumer =  
        from tblRow in table.AsEnumerable()  
        group tblRow by tblRow.Field<string>("D") into rows  
        select newrow(target, rows);  
     
    foreach (var val in enumer)  
    {  
        target.Rows.Add(val);  
        Console.WriteLine("D={0}, Sum={1}, Max={2}, Avg={3}", val.ItemArray);  

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to mark the replies as answers if they help.
    Thursday, March 12, 2009 4:58 PM
    Moderator
  • Hi  ,

    In the above example,  If Data Type for field A ( Integer / Decimal ) only know @ run time, How would I cast the column dynamically?

                 dst["SumA"] = group.Sum(row  = >  row.Field <System.Type.GetType("System.Int32") > ("A")); 

     

    Please advice


    Veeru
    Thursday, September 2, 2010 3:42 PM
  • Try this in C#:

    from t in TBL
    group t by D
    into g
    select new {
    		s = g.Sum(x => x.A),
    		m = g.Max(x => x.B),
    		a = g.Average(x => x.C)
    	}

    -- or in VB: --

    from t in TBL
    group t by key = D
    into g = group
    select 	s = g.Sum(function(x) x.A),
      	m = g.Max(function(x) x.B),
      	a = g.Average(function(x) x.C)
    

    The obvious which in VB would be:

    aggregate t in TBL into s = Sum(t.A), m = Max(t.B), a = Average(t.C)
    

    will give the same results at a higher cost (one pass for each aggregate: which means multiple select statements, one for each aggregate function), if you are running against a database.

    • Proposed as answer by Costas2 Friday, January 21, 2011 12:47 PM
    Friday, January 21, 2011 12:41 PM