Help with LINQ Query to Group and Sum Data in DataTable

Help Studio
, , , , ,
1

Hi everyone,

I need to group rows in a DataTable by a specific column, calculate the sum of another column for each group, and show only one representative row for each group along with the calculated sum.

Here is the LINQ query I’m currently using:

From row In dtDepGrouped.AsEnumerable()
Group row By key = row("GroupColumn").ToString() Into grp = Group
Let sumValueColumn = grp.Sum(Function(r) Convert.ToDouble(r("ValueColumn")))
Let firstRow = grp.First()
Select dtDepGrouped.Rows.Add({
    firstRow ("IDColumn").ToString(),
    firstRow ("CategoryColumn").ToString(),
    firstRow ("DateColumn").ToString(),
    firstRow ("AmountColumn").ToString(),
    firstRow ("BalanceColumn").ToString(),
    firstRow ("AccountColumn").ToString(),
    sumValueColumn,
    firstRow ("AssignedColumn").ToString(),
    firstRow("GroupColumn").ToString(),
    firstRow ("OrderNumberColumn").ToString(),
    firstRow ("DescriptionColumn").ToString(),
    firstRow ("SupplierColumn").ToString(),
    firstRow ("InvoiceColumn").ToString(),
    groupedRow("SegmentColumn").ToString()
})
).CopyToDataTable()

In the above query:

  • GroupColumn is the column by which I want to group the rows.
  • ValueColumn is the column for which I want to calculate the sum for each group.
  • The rest of the columns (IDColumn, CategoryColumn, DateColumn, etc.) are additional columns that I need to include in the output DataTable.

My goal is to group the rows by GroupColumn, calculate the sum of ValueColumn for each group, and show only one representative row for each group along with the sum of ValueColumn for the entire group.

However, the above query is currently adding all rows from each group to the resulting DataTable and shown to me only the first row for ‘ValueColumn’ also and I need all the SUM of whole rows for each group, which is not the output from my query. Could someone help me correct this query so that it achieves my goal?

Thanks in advance!

2

@Islam_ISmail,

Don’t have the data to test but can you try this.

Dim resultTable As DataTable = dtDepGrouped.AsEnumerable() _
    .GroupBy(Function(row) row("GroupColumn").ToString()) _
    .Select(Function(grp)
                Dim firstRow = grp.First()
                Dim sumValueColumn = grp.Sum(Function(r) Convert.ToDouble(r("ValueColumn")))
                dtDepGrouped.Rows.Add({
                    firstRow("IDColumn").ToString(),
                    firstRow("CategoryColumn").ToString(),
                    firstRow("DateColumn").ToString(),
                    firstRow("AmountColumn").ToString(),
                    firstRow("BalanceColumn").ToString(),
                    firstRow("AccountColumn").ToString(),
                    sumValueColumn,
                    firstRow("AssignedColumn").ToString(),
                    firstRow("GroupColumn").ToString(),
                    firstRow("OrderNumberColumn").ToString(),
                    firstRow("DescriptionColumn").ToString(),
                    firstRow("SupplierColumn").ToString(),
                    firstRow("InvoiceColumn").ToString(),
                    firstRow("SegmentColumn").ToString()
                })
                Return firstRow
            End Function).CopyToDataTable()

Thanks,
Ashok :slight_smile:

2 Likes
3

this query should be used inside ‘invoke code’ activity, right?

4

Generally LINQ Queries will go into Assign Activity where you will get a results set as a data type

5

@Islam_ISmail, yes

Thanks,
Ashok :slightly_smiling_face:

6

Unfortunately, the query doesn’t work. could you help please

7

@Islam_ISmail

Dim groupedRows = From row In dtDepGrouped.AsEnumerable()
                  Group row By key = row("GroupColumn").ToString() Into grp = Group
                  Let sumValueColumn = grp.Sum(Function(r) Convert.ToDouble(r("ValueColumn")))
                  Let firstRow = grp.First()
                  Select dtDepGrouped.Rows.Add({
                      firstRow("IDColumn").ToString(),
                      firstRow("CategoryColumn").ToString(),
                      firstRow("DateColumn").ToString(),
                      firstRow("AmountColumn").ToString(),
                      firstRow("BalanceColumn").ToString(),
                      firstRow("AccountColumn").ToString(),
                      sumValueColumn,
                      firstRow("AssignedColumn").ToString(),
                      firstRow("GroupColumn").ToString(),
                      firstRow("OrderNumberColumn").ToString(),
                      firstRow("DescriptionColumn").ToString(),
                      firstRow("SupplierColumn").ToString(),
                      firstRow("InvoiceColumn").ToString(),
                      firstRow("SegmentColumn").ToString()
                  })

resultDataTable  = dtDepGrouped.Clone()
For Each row As DataRow In groupedRows
    resultDataTable.ImportRow(row)
Next

Put this code inside the Invoke code pass the arguments resultDataTable as Out
dtDepGrouped as in argument

image
image867×82 4.12 KB

Hope this works for you

1 Like
8

@Islam_ISmail

Try this in assign…first use a build datatable and create the table you need with required columns and call it newdt and data lets assume is present in dt

Newdt = dt.AsEnumerable.GroupBy(function(row) row("GroupColumn").ToString).Select(function(Grp) newdt.LoadDataRow({
    Grp.First(function(x) x("IDColumn").ToString()),
    Grp.First(function(x) x("CategoryColumn").ToString()),
    Grp.First(function(x) x("DateColumn").ToString()),
    Grp.First(function(x) x("AmountColumn").ToString()),
    Grp.First(function(x) x("BalanceColumn").ToString()),
    Grp.First(function(x) x("AccountColumn").ToString()),
    Grp.Sum(Function(r) Convert.ToDouble(r("ValueColumn").ToString)).Tostring,
    Grp.First(function(x) x("AssignedColumn").ToString()),
    Grp.First(function(x) x("GroupColumn").ToString()),
    Grp.First(function(x) x("OrderNumberColumn").ToString()),
    Grp.First(function(x) x("DescriptionColumn").ToString()),
    Grp.First(function(x) x("SupplierColumn").ToString()),
    Grp.First(function(x) x("InvoiceColumn").ToString()),
    Grp.First(function(x) x("SegmentColumn").ToString())
},False)
).CopyToDataTable()

Make sure to have the column order and type as you specify in build datatable…as of now I kept all columns to string even the sum

Cheers

1 Like