sum duplicate rows value using datatable

Question

i have DataTable which can have duplicate rows. In the picture highlighted columns have all the same values. I would need to identify those rows and sum the salary( in original table is "jamekol").

and i want to sum all duplicate Name's salary value like this :

here is my orginal table : Note : i need all columns . Can anyone suggest how to do that?

DataTable new_Table = new DataTable();
                new_Table.Columns.Add("radif");
                new_Table.Columns.Add("sh_factor");
                new_Table.Columns.Add("t_factor");
                new_Table.Columns.Add("Taraf");
                new_Table.Columns.Add("m_taraf");
                new_Table.Columns.Add("mantaghe");
                new_Table.Columns.Add("jam");
                new_Table.Columns.Add("jamekol");
                new_Table.Columns.Add("m_kartkhan");
                new_Table.Columns.Add("m_takhfif");
                new_Table.Columns.Add("n_karbar");
                new_Table.Columns.Add("sh_sanad");
                new_Table.Columns.Add("code");
                new_Table.Columns.Add("m_tasvie");
                new_Table.Columns.Add("m_porsant");
                new_Table.Columns.Add("d_takhfif");
                new_Table.Columns.Add("m_maliat");
                new_Table.Columns.Add("m_avarez");
                new_Table.Columns.Add("n_taraf");
                new_Table.Columns.Add("v_moravede");
                var groupedByState = dt.AsEnumerable()
                    .GroupBy(r => r.Field<String>("Taraf"));
                foreach (var group in groupedByState)
                {
                    DataRow maxPremRow = group.OrderByDescending(r => r.Field<String>("Taraf")).First();
                    DataRow newRow = new_Table.Rows.Add();

                    newRow.SetField("id_po", group.Key);
                    newRow.SetField("Status", maxPremRow.Field<string>("Status"));
                    newRow.SetField("Cost_ex", group.Sum(r => r.Field<double?>("Cost_ex")));

                    newRow.SetField("Taraf", maxPremRow.Field<string>("Taraf"));
                    newRow.SetField("jamekol", group.Sum(r => r.Field<double?>("Cost_ex")));
                    newRow.SetField("radif", maxPremRow.Field<string>("radif"));
                    newRow.SetField("sh_factor", maxPremRow.Field<string>("sh_factor"));
                    newRow.SetField("t_factor", maxPremRow.Field<string>("t_factor"));
                    newRow.SetField("m_taraf", maxPremRow.Field<string>("m_taraf"));
                    newRow.SetField("mantaghe", maxPremRow.Field<string>("mantaghe"));
                    newRow.SetField("jam", maxPremRow.Field<string>("jam"));
                    newRow.SetField("m_kartkhan", maxPremRow.Field<string>("m_kartkhan"));
                    newRow.SetField("m_takhfif", maxPremRow.Field<string>("m_takhfif"));
                    newRow.SetField("n_karbar", maxPremRow.Field<string>("n_karbar"));
                    newRow.SetField("sh_sanad", maxPremRow.Field<string>("sh_sanad"));
                    newRow.SetField("code", maxPremRow.Field<string>("code"));
                    newRow.SetField("m_tasvie", maxPremRow.Field<string>("m_tasvie"));
                    newRow.SetField("m_porsant", maxPremRow.Field<string>("m_porsant"));
                    newRow.SetField("d_takhfif", maxPremRow.Field<string>("d_takhfif"));
                    newRow.SetField("m_maliat", maxPremRow.Field<string>("m_maliat"));
                    newRow.SetField("m_avarez", maxPremRow.Field<string>("m_avarez"));
                    newRow.SetField("n_taraf", maxPremRow.Field<string>("n_taraf"));
                    newRow.SetField("v_moravede", maxPremRow.Field<string>("v_moravede"));                       

                }
                radGridView3.DataSource = new_Table;

You need to learn about sum and group by in SQL. Use that knowledge in solving problem and post a question here is you face any problem in that. w3resource.com/sql/aggregate-functions/sum-with-group-by.php — Chetan

Random Stuff Random Stuff · Accepted Answer · 2019-12-01T08:12:22

Try this. Ive basically grouped the rows from the old datatable that had matching names and then summed the salarys and then created a new datatable with the new groupings.

DataTable new_Table = new DataTable();
        new_Table.Columns.Add("Name");
        new_Table.Columns.Add("Salary");
        new_Table.Columns.Add("Date");
        new_Table.Columns.Add("Description");

        var groupedByState = old_datatable.AsEnumerable()
            .GroupBy(r => r.Field<String>("Name"));
        foreach (var group in groupedByState)
        {
            DataRow maxPremRow = group.OrderByDescending(r => r.Field<String>("Name")).First();
            DataRow newRow = new_Table.Rows.Add();


            newRow.SetField("Name", maxPremRow.Field<string>("Name"));
            newRow.SetField("Salary", group.Sum(r => r.Field<double?>("Salary")));
            newRow.SetField("Description", maxPremRow.Field<string>("Description"));
            newRow.SetField("Date", maxPremRow.Field<string>("Date"));


        }

If there is an issue with the code could you show me the related code you have and i will implement it into it for you. :)

Can you let me know how you go?

sum duplicate rows value using datatable

2 Answers