Sum Columns In Datatable Based On Values From Another Column

I have a database table that I am using as a source for a report. The table layout looks like. Table 1 is subset of a larger database table. But for the report I only need certain

Solution 1:

You are taking the wrong approach. Do this work in SQL.

SELECT value, description, sum(hours) as hours from tblData groupby value, description;

The above will give you the second table you show in your question.

The reason to do it this way is that passing data to a client application just to have it passed right back to SQL consumes memory and system calls unnecessarily. Also, there is much less room for error, bugs, type conversion issues, etc.

If you really want to make the narrower table (which is generally a bad idea), do it like this:

SELECTvalue, description, hours from tblData into tblSkinnyData;

In general, if you needed this skinny table for some reason, you would use a VIEW on the main table.

Solution 2:

You can use AsEnumerable() in order to use LINQ, then GroupBy by Value and Description and do Sum on Hours to set for the first row:

var result = dtChartData.AsEnumerable()
            .GroupBy(row =>new
                {
                    Value = row.Field<int>("Value"),
                    Description = row.Field<string>("Description")
                })
            .Select(g =>
                {
                    var row = g.First();
                    row.SetField("Hours", g.Sum(r => r.Field<double>("Hours")));

                    return row;
                });

The result will return IEnumerable<DataRow> but if you want to get DataTable back, use:

var resultTable = result.CopyToDataTable();