data table calculation?

# data table calculation?

• Friday, December 28, 2012 5:33 PM

Hi All,

I have a below data table,

```class Program
{
static void Main(string[] args)
{
// Get the DataTable.
DataTable table = GetTable();
}

static DataTable GetTable()
{
DataTable table = new DataTable();

table.Rows.Add(1, "1.0", "A1", 9001, 20, 30, 40, 10);
table.Rows.Add(1, "1.0", "A1", 9002, 40, 20, 50, 10);
table.Rows.Add(2, "4.0", "B1", 9003, 50, 20, 70, 50);
return table;
}
}```

I need to show output like below, sum of cost base on GRP_ID and then Grand Total within C# code, please help...

1. TOTAL_COST = A_COST + B_COST

2. TOTAL_ACTUAL = A_ACTUAL + B_ACTUAL

3. % DIFF = ((TOTAL_COST - TOTAL ACTUAL)/TOTAL_COST) * 100

### All Replies

• Saturday, December 29, 2012 8:14 AM

Add new column "TOTAL_COST"

Set table.Columns["TOTAL_COST "].Expression property with required expression.

Ashutosh Tripathi

• Saturday, December 29, 2012 9:50 AM

could you please focus more?

mainly how to loop under loop...i think i need to do multi level looping...help me here

i don't want full 100% solution, just guidline with some line of code.

• Saturday, December 29, 2012 11:34 PM

Yazdani : This is not 100% solution but does this help ?

`                     var A_COST = table.AsEnumerable().Sum(x => x.Field<decimal>("A_COST"));            var B_COST = table.AsEnumerable().Sum(x => x.Field<decimal>("B_COST"));            var TOTAL_COST = A_COST + B_COST;            var A_ACTUAL = table.AsEnumerable().Sum(x => x.Field<decimal>("A_ACTUAL"));            var B_ACTUAL = table.AsEnumerable().Sum(x => x.Field<decimal>("B_ACTUAL"));            var TOTAL_ACTUAL = A_ACTUAL + B_ACTUAL;            var diff = ((TOTAL_COST - TOTAL_ACTUAL) / TOTAL_COST) * 100;            Console.WriteLine(TOTAL_COST);`

Also i did start this but could not complete using linq.. may be this is a partial solution which you can modify as needed to your needs

```public static void Main(string[] args)
{
DataTable table = GetTable();

var query = from x in table.AsEnumerable()
group x  by new
{
PROJ_ID = x["PROJ_ID"],
TOTAL_COST =  Convert.ToDecimal( x["A_COST"]) + Convert.ToDecimal(x["B_COST"]),
TOTAL_ACTUAL = Convert.ToDecimal(x["A_ACTUAL"]) + Convert.ToDecimal(x["B_ACTUAL"]),
NAME = x["SEQ"] + "_" + x["NAME"]
}
into G
select new
{
PROJ_ID= G.Key.PROJ_ID,
NAME=   G.Key.NAME,
TOTAL_COST=  G.Key.TOTAL_COST,
TOTAL_ACTUAL =    G.Key.TOTAL_ACTUAL,
DIFF = G.Key.TOTAL_ACTUAL/G.Key.TOTAL_COST * 100
};

DataTable newTable = GetNewTable();
foreach (var row in query)
{
newTable.Rows.Add(row.PROJ_ID, row.NAME, row.TOTAL_COST, row.TOTAL_ACTUAL, row.DIFF);
}
List<string> namesList = newTable.AsEnumerable().Select(x => x.Field<string>("NAME")).Distinct().ToList();

if(newTable.Rows.Count > 0)
{
var sumTotal_Cost = newTable.AsEnumerable().Sum(x => x.Field<decimal>("TOTAL_COST"));
var sumTotal_Actual = newTable.AsEnumerable().Sum(x => x.Field<decimal>("TOTAL_ACTUAL"));
DataRow totalRow = newTable.Rows.Add(0,"GRAND TOTAL",sumTotal_Cost,sumTotal_Actual,sumTotal_Actual/sumTotal_Cost * 100 );
newTable.AcceptChanges();
}
foreach (var name in namesList)
{
DataRow[] objRows = (from row in newTable.AsEnumerable()
where row["NAME"].Equals(name)
select row).ToArray();
if (objRows.Length > 0)
{
var subTOTAL_COST = newTable.AsEnumerable().Where(x => x.Field<string>("NAME").Equals(name)).Sum(x => x.Field<decimal>("TOTAL_COST"));
var subTOTAL_ACTUAL = newTable.AsEnumerable().Where(x => x.Field<string>("NAME").Equals(name)).Sum(x => x.Field<decimal>("TOTAL_ACTUAL"));
DataRow subTotalRows = newTable.Rows.Add(objRows[0]["PROJ_ID"], name + " TOTAL", subTOTAL_COST, subTOTAL_ACTUAL,  subTOTAL_ACTUAL/subTOTAL_COST * 100 );
newTable.AcceptChanges();
}
}
newTable.DefaultView.Sort = "NAME" + " " + "ASC";
newTable = newTable.DefaultView.ToTable();
foreach (DataRow row in newTable.Rows)
{
Console.WriteLine("{0} {1} {2} {3} {4}", row["NAME"], row["PROJ_ID"].ToString(),row["TOTAL_COST"], row["TOTAL_ACTUAL"], row["DIFF"]);
}

}
static DataTable GetNewTable()
{
DataTable table = new DataTable();