DataTable aggregation problem

Question

I am trying to query a DataTable inorder to calculate the sum of 2 columns in it and grouping the result by the rest of the fields. The thing is I can only get the aggregated column values and I can't get the non aggregated values.

Here's the code I am using:

var balances = from b in dtAccounts.AsEnumerable()
               group b by b.Field<decimal>("ACCOUNT_ID") into g
               select new
                      {
                          credit = g.Sum(b => b.Field<decimal>("CREDIT")),
                          debit = g.Sum(b => b.Field<decimal>("DEBIT"))
                      }

Can someone give a hint about his piece of code?

Thanks in advance

Answer 1

The range variable g has a property Key that represents the key that you have grouped by. In your case, this is b.Field<decimal>("ACCOUNT_ID"). So, if you want to have the account_id with the results, you need this query:

var balances = from b in dtAccounts.AsEnumerable()
               group b by b.Field<decimal>("ACCOUNT_ID") into g
               select new
                      {
                          accountId = g.Key,
                          credit = g.Sum(b => b.Field<decimal>("CREDIT")),
                          debit = g.Sum(b => b.Field<decimal>("DEBIT"))
                      }