0
127
ViewsDisplay Group by sum in new column
I want to display group by sum in new column in same data table. For example:
I want output to be like:
I have tried like below:
- group by sno and stored in different table.
- by using for loop, i am assigning total values to old data table.
for (int o = 0; o < returndata.Rows.Count;o++)
{
for (int i = 0; i < table.Rows.Count;i++)
{
if(returndata.Rows[o]["sno"].ToString() == table.Rows[i]["sno"].ToString())
{
table.Rows[i]["total"] = returndata.Rows[o]["total"];
}
}
}
Is there any other way to directly assign sum values using c# linq?
3 answers
Answer question0
While the question is tagged for c#, and there are many ways to do it in c#, I would just like to stress databases are more geared towards data manupulation/transformation than programming languages. Things like this should be left to database, especially when the amount of data is huge. All you need is modify the query to let database do the part.
e.g. in SQL Server, it should be simply like this:
select sno, amount, total = sum(amount) over (partition by sno) from YourTable
This will give you exactly what you are looking for, without slowing down your application.
EDIT, after OP's comment
When altering the query is not an option, the easies way to do it in .NET is to use DataTable.Compute method.
foreach (DataRow row in returndata.Rows)
{
row["total"] = returndata.Compute("sum(amount)", "sno=" + row["sno"].ToString());
}
0
Update js code in c# file,
var obj = {}
var dupdata = data.map(d => {
obj[d.sno] = obj[d.sno] + l.amount
return {...t, total: obj[d.sno]}
})
0
You can achieve this without using another Datatable and with LINQ.
Please read comments inside the code:
static void Example()
{
DataTable returndata = GetDataFromDb();
// Get distinct sno for sum
int[] distinct_sno = returndata.AsEnumerable().
Select(x => x.Field<Int32>("sno")).Distinct().ToArray();
foreach (var itm in distinct_sno)
{
// Sum all amount of each sno
var sum = returndata.AsEnumerable().Where(x => x.Field<Int32>("sno") == itm).
Sum(x=>x.Field<Int32>("amount"));
// Get all the rows that share the current sno
var results = from myRow in returndata.AsEnumerable()
where myRow.Field<int>("sno") == itm
select myRow;
// Add the sum for each total column of the current sno
foreach (var row in results)
{
row["total"] = sum;
}
}
}