Display Group by sum in new column

Question

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:

1. group by sno and stored in different table.
2. 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?

Answer 1

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());
}