Calculating SUM of "Debit" Column using Group by "TransactionNo" Column

Hello,

I have an input datatable and produce an output datatable using LINQ.
Anyone, please help.

image

Regards,
Nidhees

Hello @Palaniyappan,

“TransactionNo” column has duplicate value and accordingly, i have to SUM the “Debits” column value for each duplicate transaction no.

Regards,
Nidhees

Yay sorry… I didn’t see that clearly,
Let me check once buddy
Cheers @nidheessingh

Hope would help you @nidheessingh

Outdatatable = (from table in dt.AsEnumerable()
group table by table.Field(“TransactionNo”)
into groupedTable
select new
{TransactionNo = groupedTable.Key,
Debits = groupedTable.Sum(x => x.Field(“Debits”))}).ToArray.CopyToDatatable()

Where Outdatatable is a variable of type datatable

Kindly try this and let know whether this works or not buddy
Cheers @nidheessingh

Hi @nidheessingh

Try this as well

ExcelData.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(“Debits”).ToString.Trim) ).ToString

Thanks
Ashwin S

Hello @Palaniyappan,

I am getting compiler error on first query as below…

Regards,
Nidhees

1 Like

Aah sorry one second
Cheers @nidheessingh

Yes remove one of the curve bracket here lik this
…x.Field(“Debits”)})

Cheers @nidheessingh

Hello @Palaniyappan,

Still not working…

Regards,
Nidhees

Hello @ClaytonM,

Could you please help regarding this query?

Regards,
Nidhees

May i what was the error that occurred @nidheessingh

hello @nidheessingh,

Can you try this ?

Regards
Balamurugan.S

In your previous image, you have “=>” when declaring ‘x’, which is a C# syntax. You need to use .net syntax which would be .Sum(Function(x)
However, I think you have other issues with your LINQ code.

You can check out this post here about using GroupBy for Sum:

Regards.

Hello @balupad14,

I have used the Aggregate function “SUM” on a “Debits” column along with “Group by” on “TransactionNo” but while execution, I am getting error on row no-101755.

image

I checked the data on these specific cells(i.e. “Debits” and “TransactionNo” columns) and everything is OK.

Input Sheet:

Could you please help me where is the issue?

Regards,
Nidhees

Hi ,

Can you attach the file to test at my side.

Regards
Balamurugan.S

Hello @balupad14,

Please find the updated sheet with few data.
In this sheet, I am getting an error at row# 18.

Regards,
Nidhees

BS_Input.xlsx (10.0 KB)

1 Like

Hi @nidheessingh,
I will let you . after it verified.

Thank you
Balamurugan.S

Hi @nidheessingh ,

The problem is here

The cells should not be blank. It must have the value. I have set it as zero.
It will give result like below

image

Sample : nidheessingh.zip (9.5 KB)

Regards
Balamurugan.S