CODEWITHSUNDEEP
c#asp.net
Developer
Developer

Reputation: 8636

How to remove the duplicate rows by summing up the required columns in a datatable

Hi all I am having my data table which is from my database is as follows

Name       Total
XYZ         20
XYZ         20
ABC         20

Now I would like to have my data table as follows

Name       Total
XYZ         40
ABC         20

I tried this linq from here Find duplicate and merge record into single datatable c# which works fine but as I am having my values from database I don't know the type of the variable so can some one help me and give me the solution in non-linq way

Upvotes: 0

Views: 1674

Answers (2)

MVCKarl
MVCKarl

Reputation: 1295

If you have two tables and you want to combine them all then the below is what you are after

SELECT bothTables.Name, SUM(total) FROM
(
   SELECT Name, SUM(total) as total FROM Table_1 GROUP BY Name
   UNION ALL
   SELECT Name, SUM(total) as total FROM Table_2 GROUP BY Name
) AS bothTables
GROUP BY bothTables.Name
ORDER BY bothTables.Name desc

or if you want to do it using your Data Table (dt in this example)

var summedValues = from table in dt.AsEnumerable()
                   group table by table.Field<string>("Name")
                   into groupedTable
                   select new
                             {
                                Name = groupedTable.Key,
                                Total = groupedTable.Sum(x => x.Field<int>("Total"))
                             };

Upvotes: 1

InfoSolve Pty Ltd
InfoSolve Pty Ltd

Reputation: 3

SQL version of the solution would be:

select Name, sum(Total) group by Name

Upvotes: 0

Related Questions