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:

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?

Pradeep Kumar · Answer 1 · 2021-03-19T10:27:57.677

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

Yes I agree with your point but I am getting data from an API call and storing it in a data table. So, I cannot access database. — Bin, Mar 19 '21 at 08:55
@Bin, I just updated my answer to show the programmatic way to do it. — Pradeep Kumar, Mar 19 '21 at 10:12

score 1 · Answer 2 · answered Mar 19 '21 at 16:28

1

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]}
})

answered Mar 19 '21 at 16:28

Hema Latha

41
4

score 0 · Answer 3 · answered Mar 19 '21 at 07:28

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

score 0 · Answer 4 · answered Mar 19 '21 at 09:12

If your JSON object is like this,

let d = [
    {sno: 1, amount: 10},
    {sno: 1, amount: 20},
    {sno: 2, amount: 20},
    {sno: 2, amount: 20},
    {sno: 3, amount: 10},
    {sno: 3, amount: 15},
    {sno: 1, amount: 20},
    {sno: 3, amount: 6},
    {sno: 4, amount: 20},
    {sno: 4, amount: 19},
]

let tableData = {}
td = d.map(l => {
    if(!d[l.sno])
    d[l.sno] = 0
    d[l.sno] = d[l.sno] + l.amount
})

let td = d.map(t => {
    return {...t, total: tableData[t.sno]}
})

Display Group by sum in new column

4 Answers4