How To Compute DataTable With Running Total Per Group In C#

Good Evening!

There was a question raised by a developer if it's possible to compute the running total of a specific DataTable column per grouping or category. The solution is to mark the category as a flag variable that indicates the group to be calculated. The sample code below creates a DataTable object with fictitious information of hardware materials that belong to a particular group specifically PartyCode column. If a column belongs to that group, it will perform calculations. If not, reset the flag variable and total so as to perform new calculations. When computing totals for large number of records, make sure to sort the DataTable using it's category.

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[5] 
    { new DataColumn("SrNo", typeof(int)),
   new DataColumn("PartyName", typeof(string)),
   new DataColumn("ItemName",typeof(string)),
   new DataColumn("ChqAmt", typeof(int)),
   new DataColumn("PartyCode",typeof(string))});
dt.Rows.Add(1, "ABC Water Supply", "Construction Service", 400,"ABC");
dt.Rows.Add(2, "ABC Pump Services", "Type 24 Engine Pump", 150, "ABC");
dt.Rows.Add(3, "ABC Water Supply", "12 Ft Water Tank", 600, "ABC");
dt.Rows.Add(4, "XYZ Hardware", "Garden Soil", 250, "XYZ");
dt.Rows.Add(5, "XYZ Hardware", "Power Generator", 245, "XYZ");
dt.Rows.Add(6, "ACE Hardware", "Screw Driver", 16, "ACE");
dt.Rows.Add(7, "ACE Hardware", "8W Led Bulb", 18, "ACE");

string flag = dt.Rows[0][4].ToString();
int subTotal = 0;
int grandTotal = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
 if (dt.Rows[i][4].ToString() != flag)
 {
  DataRow toInsert = dt.NewRow();
  toInsert[0] = DBNull.Value;
  toInsert[1] = string.Format("Total For {0}", dt.Rows[i-1][1].ToString());
  toInsert[2] = "";
  toInsert[3] = subTotal;
  toInsert[4] = null;
  dt.Rows.InsertAt(toInsert, i);
  grandTotal += subTotal;
  subTotal = 0;
  flag = dt.Rows[i+1][4].ToString();
  continue;
 }
 else
 {
  subTotal += Convert.ToInt32(dt.Rows[i][3].ToString());
  if (i == dt.Rows.Count - 1)
  {
   DataRow toInsert = dt.NewRow();
   toInsert[0] = DBNull.Value;
   toInsert[1] = string.Format("Total For {0}", dt.Rows[i][1].ToString());
   toInsert[2] = "";
   toInsert[3] = subTotal;
   toInsert[4] = null;
   dt.Rows.InsertAt(toInsert, i+1);
   grandTotal += subTotal;

   //insert grand total
   int totalIndex = i + 1;
   DataRow grandTotalInsert = dt.NewRow();
   grandTotalInsert[0] = DBNull.Value;
   grandTotalInsert[1] = "Grand Total";
   grandTotalInsert[2] = "";
   grandTotalInsert[3] = string.Format("{0}", grandTotal);
   grandTotalInsert[4] = null;
   dt.Rows.InsertAt(grandTotalInsert, totalIndex + 1);
   break;
  }
 }
}

GridView1.DataSource = dt;
GridView1.DataBind();

Output