Sum data table columns using linq

Question

Hi all I am having my data in datatable as follows

SKU  Quantity  UnitPrice  LinePrice
 A     10         2         20
 A     10         2         20
 B     10         2         40

I would like to sum up the SKU with duplicate records and get the result as follows

SKU  Quantity  UnitPrice  LinePrice
 A     20         2         40
 B     10         2         40

I tried this but no luck

var query = from row in lDTSalesOrder.AsEnumerable()
                            group row by row.Field<string>("SKU") into grp
                            orderby grp.Key
                            select new
                            {
                                Id = grp.Key,
                                Sum = grp.Sum(r => r.Field<double>("UnitPrice"))
                            };

Solution

In your sample output you haven't actually summed the Unit Price, but it sounds like what you want is something like this:

var results = 
    from row in lDTSalesOrder
    group row by row.Field<string>("SKU") into grp
    orderby grp.Key
    select new
    {
        SKU = grp.Key,
        Quantity = grp.Sum(r => r.Field<double>("Quantity")),
        UnitPrice = grp.Sum(r => r.Field<double>("UnitPrice")),
        LinePrice = grp.Sum(r => r.Field<double>("LinePrice"))
    };

This will produce:

SKU  Quantity  UnitPrice  LinePrice
 A     20         4         40
 B     10         2         40

---

If you really want to produce a Unit Price of 2 for the first result record, Sum is not what you want; you'll have to to use some other aggregate function. Min, Max, Average, or even Select(...).First() would produce that result, but you'd have to more precisely define what you want in your output. You could also do this:

var results = 
    from row in lDTSalesOrder
    group row by new { SKU = row.Field<string>("SKU"), UnitPrice = row.Field<string>("UnitPrice") } into grp
    orderby grp.Key.SKU
    select new
    {
        SKU = grp.Key.SKU,
        Quantity = grp.Sum(r => r.Field<double>("Quantity")),
        UnitPrice = grp.Key.UnitPrice,
        LinePrice = grp.Sum(r => r.Field<double>("LinePrice"))
    };

Any of these options will produce:

SKU  Quantity  UnitPrice  LinePrice
 A     20         2         40
 B     10         2         40