Linq query for groupby and sum

I have a datatable as below. I want to group by using Col3 and take sum of Col2.

Input Datatable:

Col1 – Col2 – Col3 – Col4
A – 11 – AA – XYZ
B – 12 – RT – XYZ
C – 13 – HL – XYZ
D – 27 – AA – XYZ
E – 17 – AA – XYZ
F – 15 – RT – XYZ
G – 24 – HL – XYZ

OutPut required:

Col1 – Col2
AA – 55
RT – 27
HL – 37

Please help me with the LINQ query.

@ppr @arivu96 @ClaytonM

Check this:

1 Like

@Sugumar8785 - pls try below -

Build a datatable with same format and data -> name as dtData
dtResults = dtData.Clone

dtResults = (From dte In dtTable.AsEnumerable
Group dte By col1=dte(2).ToString.Trim Into Group
Select dtResults.Rows.Add({col1, Group.Sum(Function (x) CInt(x(1).toString.Trim))})).CopyToDataTable

you can get the same output

Col1 – Col2
AA – 55
RT – 27
HL – 37

let me know if you find an issues…

3 Likes

@Sugumar8785
my linq would look same like this one from GBK. Is it running?

Dont miss out to modify dtResult after cloning and do remove the unneeded columns Col1,Col4. Another option wouldbe to use buld datatable and modelling the structure as needed by dtResult. For getting it clean, use a clear datatable afterwards as often an empty row will be present

1 Like

Thanks, How can I extend this query to multiple columns?
Say if I will have to do sum of few more columns as well.

Thank you. How can I extend this query to multiple columns?
Say if I will have to do sum of few more columns as well.

@ppr Thank you. How can I extend this query to multiple columns?
Say if I will have to do sum of few more columns as well.

@Sugumar8785
in that case I would suggest


grafik
grafik

Groups = (From dte In dtData.AsEnumerable
Group dte By col1=dte(2).ToString.Trim Into grp=Group
Select grp.toList).toList

for Key Columns: grp.First()(KeyColumnNameOrIndex)
for Aggregations e.g Sum: grp.Sum(Function (x) CInt(x(ColIndexOrName).toString.Trim))

This split allows you a more control on bug fixing and maintenance

2 Likes

@ppr

One day, we’ll have to scroll to read your one-liners :stuck_out_tongue_winking_eye:

1 Like

2 Likes

@ppr Thanks. However, It creates output for one column only as previous query given by GBK.

@Sugumar8785
It was only a sample that you will enhance with the other columns.

1 Like

@GBK I have extended your query for other columns as below. But If any columns are having empty values, it is throwing exception. How can I prevent this issue?

dtResults = (From dte In dtTable.AsEnumerable
Group dte By col1=dte(2).ToString.Trim Into Group
Select dtResults.Rows.Add({col1, Group.Sum(Function (x) CInt(x(1).toString.Trim)), Group.Sum(Function (x) CInt(x(2).toString.Trim)) , Group.Sum(Function (x) CInt(x(3).toString.Trim))})).CopyToDataTable

Exception: Conversion from string “” to type ‘Double’ is not valid.

Thank you @ppr

If possible, please help me with above query.

@Sugumar8785- that linq is dedicated for the earlier 2 column output…

1 Like

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