none
Method to add subtotals or summing duplicate rows from Datatable RRS feed

  • Question

  • Hi, I have two Datatables , "order" and "Summary". I want to make a subtotal summary of order in the summary table. I am planning to use following code but i wanted to know is there a better solution without looping or using expression column for this purpose?


    Dim SummTable as new Datatable
    ...code for adding columns
    
    For stRow as Integer = 0 To SummTable.Rows.Count - 1
    
    	With Tables(Order)
    	      Dim fltr As String = SummTable.Rows(stRow).Item("ID")
    	      Dim sum As Double = CDbl(.Compute("SUM(TotalCost)", fltr))		
    	End with
    	
    	SummTable.Rows(stRow).Item("TotalCost") = sum
    
    Next
    Thanks



    • Edited by Shan1986 Wednesday, September 18, 2019 10:23 AM
    Wednesday, September 18, 2019 10:22 AM

Answers

  • Here is an example that has some setup so you can try the code out, dtResults holds the totals.

    Private Function GetMockedData() As DataTable
        Dim dt As New DataTable With {.TableName = "MyTable"}
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Identifier",
                          .DataType = GetType(Integer),
                          .AutoIncrement = True,
                          .AutoIncrementSeed = 1
                          }
            )
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Name",
                          .DataType = GetType(String)
                          }
            )
    
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Company",
                          .DataType = GetType(String)
                          }
            )
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Year",
                          .DataType = GetType(Integer)
                          }
            )
    
    
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Month",
                          .DataType = GetType(Integer)
                          }
            )
    
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Total",
                          .DataType = GetType(Decimal)
                          }
            )
    
        dt.Rows.Add(New Object() {Nothing, "Google", "Bill", 2019, 1, 1200D})
        dt.Rows.Add(New Object() {Nothing, "Amazon", "Anne", 2017, 7, 2200D})
        dt.Rows.Add(New Object() {Nothing, "Google", "Bill", 2018, 2, 3300D})
        dt.Rows.Add(New Object() {Nothing, "Amazon", "Anne", 2016, 12, 6700D})
        dt.Rows.Add(New Object() {Nothing, "Google", "Bill", 2017, 4, 9200D})
        dt.Rows.Add(New Object() {Nothing, "Amazon", "Anne", 2015, 4, 8200D})
    
        Return dt
    
    End Function
    Public Function ResultsDataTable() As DataTable
        Dim dtTotals As New DataTable
        dtTotals.Columns.Add(New DataColumn() With {.ColumnName = "Identifier", .DataType = GetType(Integer)})
        dtTotals.Columns.Add(New DataColumn() With {.ColumnName = "TotalCost", .DataType = GetType(Decimal)})
        Return dtTotals
    End Function
    Public Sub GetGroups()
        Dim dtTotals = ResultsDataTable()
    
        Dim results = GetMockedData().AsEnumerable.GroupBy(
                Function(student) student.Field(Of String)("Name")) _
            .Select(Function(group) New With
                       {
                       .Name = group.Key,
                       .Rows = group})
    
        For Each item In results
    
            If item.Rows.Count() > 0 Then
                dtTotals.Rows.Add(item.Rows.FirstOrDefault().Field(Of Integer)("Identifier"),
                                  item.Rows.Select(Function(row) row.Field(Of Decimal)("total")).Sum())
    
            End If
        Next
    
    End Sub


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Shan1986 Wednesday, September 18, 2019 8:11 PM
    Wednesday, September 18, 2019 11:07 AM
    Moderator

All replies

  • Here is an example that has some setup so you can try the code out, dtResults holds the totals.

    Private Function GetMockedData() As DataTable
        Dim dt As New DataTable With {.TableName = "MyTable"}
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Identifier",
                          .DataType = GetType(Integer),
                          .AutoIncrement = True,
                          .AutoIncrementSeed = 1
                          }
            )
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Name",
                          .DataType = GetType(String)
                          }
            )
    
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Company",
                          .DataType = GetType(String)
                          }
            )
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Year",
                          .DataType = GetType(Integer)
                          }
            )
    
    
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Month",
                          .DataType = GetType(Integer)
                          }
            )
    
        dt.Columns.Add(
            New DataColumn With
                          {
                          .ColumnName = "Total",
                          .DataType = GetType(Decimal)
                          }
            )
    
        dt.Rows.Add(New Object() {Nothing, "Google", "Bill", 2019, 1, 1200D})
        dt.Rows.Add(New Object() {Nothing, "Amazon", "Anne", 2017, 7, 2200D})
        dt.Rows.Add(New Object() {Nothing, "Google", "Bill", 2018, 2, 3300D})
        dt.Rows.Add(New Object() {Nothing, "Amazon", "Anne", 2016, 12, 6700D})
        dt.Rows.Add(New Object() {Nothing, "Google", "Bill", 2017, 4, 9200D})
        dt.Rows.Add(New Object() {Nothing, "Amazon", "Anne", 2015, 4, 8200D})
    
        Return dt
    
    End Function
    Public Function ResultsDataTable() As DataTable
        Dim dtTotals As New DataTable
        dtTotals.Columns.Add(New DataColumn() With {.ColumnName = "Identifier", .DataType = GetType(Integer)})
        dtTotals.Columns.Add(New DataColumn() With {.ColumnName = "TotalCost", .DataType = GetType(Decimal)})
        Return dtTotals
    End Function
    Public Sub GetGroups()
        Dim dtTotals = ResultsDataTable()
    
        Dim results = GetMockedData().AsEnumerable.GroupBy(
                Function(student) student.Field(Of String)("Name")) _
            .Select(Function(group) New With
                       {
                       .Name = group.Key,
                       .Rows = group})
    
        For Each item In results
    
            If item.Rows.Count() > 0 Then
                dtTotals.Rows.Add(item.Rows.FirstOrDefault().Field(Of Integer)("Identifier"),
                                  item.Rows.Select(Function(row) row.Field(Of Decimal)("total")).Sum())
    
            End If
        Next
    
    End Sub


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Shan1986 Wednesday, September 18, 2019 8:11 PM
    Wednesday, September 18, 2019 11:07 AM
    Moderator
  • Hi, I have two Datatables , "order" and "Summary". I want to make a subtotal summary of order in the summary table. I am planning to use following code but i wanted to know is there a better solution without looping or using expression column for this purpose?Next

    Thanks



    Shan,

    With LINQ we got more methods to solve the task you want to do. However, remember what they do is looping behind the scene. (But do it quicker). This kind of problems can never be done without looping. 

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/queries-in-linq-to-dataset


    Success
    Cor

    Wednesday, September 18, 2019 2:58 PM