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

Question

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

Explain how are you accessing DB. It's hard to understand that you don't know column type. — Denis Agarev, Nov 28 '12 at 06:51
Column type in the sense in the linq as per the link he is writing some thing like `r.Field` but as I am having my data from database and assigining it to data table how can I know that `Field` — Developer, Nov 28 '12 at 06:54
are you sure that the column names are "Name and "Total" always? and what does this mean *I don't know the type of the variable*? — naveen, Nov 28 '12 at 07:48
`Naveen` the linq expression is working when we declare data table as follow `DataTable table1 = new DataTable("patients"); table1.Columns.Add("id", typeof(int));` but as I am assigning the datatable from db how can we make sure what datatype exists at that point — Developer, Nov 28 '12 at 08:06

MVCKarl · Accepted Answer · 2012-11-28T08:00:09.920

1

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

edited Nov 28 '12 at 08:00

answered Nov 28 '12 at 07:21

MVCKarl

1,283
9
7

Rather than getting the values from sql itself is there any other option to do in datatable it self – Developer Nov 28 '12 at 07:28
I am not sure what you mean. Try the Datatable example I have provided you. – MVCKarl Nov 28 '12 at 08:00
This is what I am asking but `x.Field("Total"))` here `int` we have to declare while we are assigning to datatable but directly it is not allwing – Developer Nov 28 '12 at 08:03
Are you saying they are coming out as string values? – MVCKarl Nov 28 '12 at 08:06
Not sure but if i execute it i am getting an exception as Invalid cast exception, this will work only when when we define our column as `int` like `dt.Columns.Add("id", typeof(int));` – Developer Nov 28 '12 at 08:07
how are you defining the Total column? – MVCKarl Nov 28 '12 at 08:09
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/20223/discussion-between-user-and-mvckarl) – Developer Nov 28 '12 at 08:10

score 0 · Answer 2 · edited Nov 28 '12 at 07:11

0

SQL version of the solution would be:

select Name, sum(Total) group by Name

edited Nov 28 '12 at 07:11

akjoshi

15,374
13
103
121

answered Nov 28 '12 at 06:55

InfoSolve Pty Ltd

3
1

1

It's not from a unique table if so I had definitely implemented this, will get data from different tables will have some more columns in data table but as per my req i posted the sample datatable here – Developer Nov 28 '12 at 06:59
Can you be more clear. Do you want to do this from the Database or from the DataTable? Also how many tables is it coming from and what are the names of the tables? – MVCKarl Nov 28 '12 at 07:12
I would like to achieve it from datatable – Developer Nov 28 '12 at 07:23

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

2 Answers2