Linq query group by and sum

Hello guys,

I need to do a linq query group by Name and sum Amount.
Here is a sample of my excel DTClean.xlsx (8.7 KB)

For the output, i need to keep all the columns.
I have tryied with an Balareva activity taht group by and sum but the output keeps only the 2 columns.

I tryied with a linq query but something is wrong, here is what I tryed (following previous topics)

(From dte In dtData.AsEnumerable
Group dte By dtData.Columns(“NAME”).ToString.Trim Into Group
Select dtResults.Rows.Add({dtData.Columns(“NAME”), Group.Sum(Function (x) CDbl(x(5).toString.Trim))})).CopyToDataTable

Can some one help me out please ?

@ppr @MAHESH1

Thank you in advance

Regards,

N.

@najoua.abbaci
providing sample out put always helps to speed up the work on the solution approach. However find a first suggestion:

(From dte In dtData.AsEnumerable
Group dte By k1= dtData.Columns(“NAME”).ToString.Trim Into grp= Group
Select dtResults.Rows.Add({k1, grp.Sum(Function (x) CDbl(x(“Amount”).toString.Trim))})).CopyToDataTable

with the assumption dtResults is empty and having 2 cols reflecting: Name, Sum/Amount

1 Like

Thank you for your answer.
Sorry I should have put more details indeed.

I have filtered a Dt → output dt is DtData
image

My DtResults (clone frome dtData) has the following columns :
image

So my final output needs to keeps all the columns but group by name and sum the amount.

Hope it is more clear now :blush:

@najoua.abbaci
just extend the itemarray within the LINQ:

(From d In dtData.AsEnumerable
Group d By k1= dtData.Columns(“NAME”).ToString.Trim Into grp= Group
let s = grp.Sum(Function (x) CDbl(x(“Amount”).toString.Trim))
Let ra = new Object(){grp.First()(0),grp.First()(1),grp.First()(2)…,k1, s, grp.First()(6)… }
Select dtResults.Rows.Add(ra)).CopyToDataTable

2 Likes

Thanks.
I have a error message : “Assign: Input array is longer than the number of columns in this table.”
but it is surely because i am not sure what to put after this :
Let ra = new Object(){grp.First()(0),grp.First()(1),grp.First()(2)…,k1, s, grp.First()(6)… }
I understood that i need to put all the columns grp.First()(3) etc… but at the end I am not sure "grp.First()(6)… what should I put after ?
Thank you again for your help


just complete the itemArray as by your needs

1 Like

thank you

I change a little and now it is working thank you again @ppr

(From d In dtData.AsEnumerable
Group d By k1= d.item(“Société_Coala”).ToString Into grp= Group
Let s = grp.Sum(Function (x) CDbl(x(“Montant HT”).toString.Trim))
Let ra = New Object(){grp.First()(0),grp.First()(1),grp.First()(2),grp.First()(3),k1,s, grp.First()(6),grp.First()(7),grp.First()(8)}
Select dt_Asset.Rows.Add(ra)).CopyToDataTable

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.