DataTable Group By Sum and Count using Linq

Hello,
I am having a problem writing a Linq that could achieve something like this

SELECT EmployeeName, SUM(Transaction), COUNT(*)
FROM Transactions
GROUP BY EmployeeName

I found a temporary (ugly) solution for that but I would like to make it the right way.

Things to mention

  1. Transactions is a DataTable.
  2. To use Sum i have to fist replace “,” (coma) with “.” (dot) →
    Sum(Function(x) Convert.ToDouble(x(“Transaction”).ToString.Trim.Replace(“,”, “.”))

Regards,
Bazyl

Hi.

Normally, if you are processing the data by employee name (in your case), you would want to filter your data set to the items for each employee. Then, you can simply use .Sum() on each employee as you process it. This simple method would look something like this:

For each empl In Transactions.AsEnumerable.Select(Function(r) r("EmployeeName").ToString ).ToArray.Distinct
    sumTrx = Transactions.AsEnumerable.Where(Function(r) r("EmployeeName").ToString = empl ).ToArray.Sum(Function(r) If(IsNumeric(r("Transaction").ToString.Trim), CDbl(r("Transaction").ToString.Trim, 0) )
    countTrx = Transactions.AsEnumerable.Where(Function(r) r("EmployeeName").ToString = empl ).ToArray.Count

However, if you would like to return this information using Group By, then I would say check out the information here:

Or there might be other posts out there. - If you need further help getting GroupBy to work, then let us know; I’m “not” really an expert using GroupBy.

Regards.

1 Like

Hey @Bazyleusz

Have a look in an existing thread, That might help you at some extent i guess :slight_smile:

Regards…!!
Aksh