Add a new row to DataTable for sub total

Question

I have datatable like this

| Project Name | Source |   Source Amount
         A         Abc            $10000
         B         Xyz            $12990
         C         Mnf            $10000
         A         Dqe            $50200
         B         Pgp            $14000
         A         Rsy            $27000

and I need to group by rows for sub total totals in between as

| Project Name | Source |   Source Amount
        A         Abc            $10000
        A         Dqe            $50200
        A         Rsy            $27000
  Total of A                     $87200
        B         Xyz            $12990
        B         Pgp            $14000
  Total of B                     $26990
        C         Mnf            $10000
  Total of C                     $10000
  All Total                      $124190

I used groupBy and sum in lambda but couldnot figureout how to include a new row in the existing datatable from withing the lambda expression.

Code I was using like

   dt.AsEnumerable()
                .GroupBy(d => d.Field<string>("Project Name"))
                .Sum(d=>d.Field<string>("Source Amount"))

Answer 1

Here is one way to do it:

    // Find distinct projects
    var distinctProjects = (from r in dt.AsEnumerable()
        select r["ProjectName"]).Distinct().OrderBy(project => project);

    // Get all amounts to tally later for the All Total entry
    var allEntries = from r in dt.AsEnumerable()
        select r["SourceAmount"];

    // Loop through distinct project list and add project subtotal row to table
    foreach (var p in distinctProjects)
    {
        var amt = from r in dt.AsEnumerable()
            where r["ProjectName"] == p
            select r["SourceAmount"];

        DataRow dr = dt.NewRow();
        dr["ProjectName"] = p + " Total:";
        dr["SourceAmount"] = amt.Sum(x => Convert.ToDecimal(x));
        dt.Rows.Add(dr);
    }

    // Sort table so the sub totals fall under the project it belongs to
    DataView dv = dt.DefaultView;
    dv.Sort = "ProjectName ASC, Source ASC";
    dt = dv.ToTable();

    // Create and add the final total row
    DataRow finalTotal = dt.NewRow();
    finalTotal["ProjectName"] = "All Total:";
    finalTotal["SourceAmount"] = allEntries.Sum(x => Convert.ToDecimal(x));
    dt.Rows.Add(finalTotal);

    // Display correct results with message box
    foreach (DataRow r in dt.Rows) {
        MessageBox.Show(
            r["ProjectName"].ToString() + "    " +
            r["Source"].ToString() + "    " +
            r["SourceAmount"].ToString()
        );
    }

Code to build datatable:

    //Build Data Table
    DataTable dt = new DataTable();
    dt.Columns.Add("ProjectName");
    dt.Columns.Add("Source");
    dt.Columns.Add("SourceAmount");

    // Add Rows
    DataRow rowA1 = dt.NewRow();
    rowA1["ProjectName"] = "A";
    rowA1["Source"] = "ABC";
    rowA1["SourceAmount"] = "10000";
    dt.Rows.Add(rowA1);
    DataRow rowA2 = dt.NewRow();
    rowA2["ProjectName"] = "A";
    rowA2["Source"] = "Dqe";
    rowA2["SourceAmount"] = "50200";
    dt.Rows.Add(rowA2);
    DataRow rowA3 = dt.NewRow();
    rowA3["ProjectName"] = "A";
    rowA3["Source"] = "Rsy";
    rowA3["SourceAmount"] = "27000";
    dt.Rows.Add(rowA3);
    DataRow rowB1 = dt.NewRow();
    rowB1["ProjectName"] = "B";
    rowB1["Source"] = "Xyz";
    rowB1["SourceAmount"] = "12990";
    dt.Rows.Add(rowB1);
    DataRow rowB2 = dt.NewRow();
    rowB2["ProjectName"] = "B";
    rowB2["Source"] = "Pgp";
    rowB2["SourceAmount"] = "14000";
    dt.Rows.Add(rowB2);
    DataRow rowC1 = dt.NewRow();
    rowC1["ProjectName"] = "C";
    rowC1["Source"] = "Mnf";
    rowC1["SourceAmount"] = "10000";
    dt.Rows.Add(rowC1);

Resources:

https://stackoverflow.com/a/4351889/1967692

https://stackoverflow.com/a/3521137/1967692