none
Linq Group and Sum RRS feed

  • Question

  • Hi,

    I am not familiar with LINQ and I have a problem with this group and sum, it's not working:

     Dim query = From row In DxPart From Imp In row.ImpFED
                                Group row By dateGroup = New With {Key .xType = Imp.vti_Type,
                                                                   Key .xCode = Imp.vti_Code,
                                                                   Key .xFact = Imp.vti_Factor,
                                                                   Key .xRate = Imp.vti_Rate} Into Group
                                Select New With {
                                          Key .Dates = dateGroup,
                                          .SumAmount = Group.Sum(Function(x) x.ImpFED(0).vti_Total)}

    So in my object DxPart I have two ImpFED objects:

    Imp.vti_Type = "T"
    Imp.vti_Code = "002"
    Imp.vti_Factor = "Rate"
    Imp.vti_Rate = 0.837
    Imp.vti_Total = 12.35


    Imp.vti_Type = "T"
    Imp.vti_Code = "002"
    Imp.vti_Factor = "Exte"
    Imp.vti_Rate = 0.0
    Imp.vti_Total = 0.0

    So I should get back:

    xType = "T"
    xCode = "002"
    xFact = "Rate"
    xRate = 0.837
    SumAmount = 12.35


    xType = "T"
    xCode = "002"
    xFact = "Exte"
    xRate = 0.0
    SumAmount = 0.0

    but I am only getting:

    xType = "T"
    xCode = "002"
    xFact = "Exte"
    xRate = 0.0
    SumAmount = 0.0

    Any ideas what I am doing wrong?

    Thanks in advance.


    G.Waters

    Sunday, December 23, 2018 8:45 AM

Answers

  • Hi Karen,

    Thanks for your example, but looks like you are using a Datatable and I need to use custom objects, in this case DxPart is a list of object that each can have several ImpFED objects.

    I have realized that my error is in this part:

    Select New With {
           Key .Dates = dateGroup,
           .SumAmount = Group.Sum(Function(x) x.ImpFED(0).vti_Total)}

    Because I am always selecting the first group (x.ImpFED(0))

    Any idea how to Sum all groups?


    G.Waters

    By understanding Grouping it's so much easier to group/sum. So to move from DataTable to custom list for instance where the following is our concrete class (we can have more properties also).

    Public Class Item
        Public Property TaxRate() As Decimal
        Public Property Value() As Decimal
        Public Property TaxAmount() As Decimal
    End Class

    This (from my first reply) will not work as Data is working against a DataRow 

    Public Class GroupData
        Public Property TaxRate As Decimal
        Public Property TaxRateSum As Decimal
        Public Property ValueSum As Decimal
        Public Property TaxAmountSum As Decimal
        Public Property Data As IGrouping(Of Decimal, DataRow)
    End Class
    

    So we adjust for Item

    Public Class GroupDataItem
        Public Property TaxRate As Decimal
        Public Property TaxRateSum As Decimal
        Public Property ValueSum As Decimal
        Public Property TaxAmountSum As Decimal
        Public Property Data As IGrouping(Of Decimal, Item)
    End Class
    

    In the following itemList is a List(Of Item) we move from DataTable to itemList and properties of Item.

    Dim results = itemList.GroupBy(
      Function(row) row.TaxRate) _
        .Select(Function(group) New GroupDataItem With
                   {
                   .TaxRate = group.Key,
                   .TaxRateSum = group.Sum(Function(row) row.TaxRate),
                   .ValueSum = group.Sum(Function(row) row.Value),
                   .TaxAmountSum = group.Sum(Function(row) row.TaxAmount),
                   .Data = group
                   })
    
    For Each group As GroupDataItem In results
        DataGridView2.Rows.Add(New Object() _
                              {
                                  group.TaxRate,
                                  group.ValueSum,
                                  group.TaxAmountSum,
                                  group.ValueSum + group.TaxAmountSum
                              })
    Next
    So with that you should be able to work your task out.  As mentioned above the key is not to simply try but instead to understand grouping/summing. Grouping and summing was around way before LINQ and Lambda :-)


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by George Waters Monday, December 24, 2018 12:54 AM
    Sunday, December 23, 2018 9:29 PM
    Moderator

All replies

  • Hello,

    Here is a stock example that can assist to better understand group/sum and note I strong type the grouping since this shows the possibility to do the group/sum operation outside the form if so desire.

    Class for grouping

    Public Class GroupData
        Public Property TaxRate As Decimal
        Public Property TaxRateSum As Decimal
        Public Property ValueSum As Decimal
        Public Property TaxAmountSum As Decimal
        Public Property Data As IGrouping(Of Decimal, DataRow)
    End Class

    Form code

    Option Infer On
    Option Strict On
    Public Class Form1
        ''' <summary>
        ''' Load DataGridView with mocked up data
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable With {.TableName = "GroupDemoTable"}
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "TaxRate",
                              .DataType = GetType(Decimal)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Value",
                              .DataType = GetType(Decimal)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "TaxAmount",
                              .DataType = GetType(Decimal)})
    
            dt.Rows.Add(New Object() {5D, 2000D, 100D})
            dt.Rows.Add(New Object() {10D, 1000D, 100D})
            dt.Rows.Add(New Object() {15D, 1000D, 150D})
            dt.Rows.Add(New Object() {10D, 2000D, 200D})
            dt.Rows.Add(New Object() {5D, 5000D, 50D})
            dt.Rows.Add(New Object() {15D, 2000D, 300D})
    
            DataGridView1.DataSource = dt
        End Sub
        ''' <summary>
        ''' Perform group operation.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            DataGridView2.Rows.Clear()
    
            Dim dt = CType(DataGridView1.DataSource, DataTable)
    
            Dim results = dt.AsEnumerable.GroupBy(
                Function(row) row.Field(Of Decimal)("TaxRate")) _
                    .Select(Function(group) New GroupData With
                       {
                           .TaxRate = group.Key,
                           .TaxRateSum = group.Sum(Function(row) row.Field(Of Decimal)("TaxRate")),
                           .ValueSum = group.Sum(Function(row) row.Field(Of Decimal)("Value")),
                           .TaxAmountSum = group.Sum(Function(row) row.Field(Of Decimal)("TaxAmount")),
                           .Data = group
                       })
    
    
            For Each group As GroupData In results
                DataGridView2.Rows.Add(New Object() _
                                          {
                                              group.TaxRate,
                                              group.ValueSum,
                                              group.TaxAmountSum,
                                              group.ValueSum + group.TaxAmountSum
                                          })
            Next
        End Sub
    End Class
    
    

    Results of group/sum screenshot (forum is having issues uploading images so here it is on Microsoft OneDrive)

    https://1drv.ms/u/s!AtGAgKKpqdWjjHM7uJls29EtXXX6


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Sunday, December 23, 2018 10:58 AM
    Moderator
  • Hi Karen,

    Thanks for your example, but looks like you are using a Datatable and I need to use custom objects, in this case DxPart is a list of object that each can have several ImpFED objects.

    I found out what the problem was, I was grouping the parent object instead of the childs

    Dim query = From row In DxPart From Imp In row.ImpFED
                                Group Imp By dateGroup = New With {Key .xType = Imp.vti_Type,
                                                                   Key .xCode = Imp.vti_Code,
                                                                   Key .xFact = Imp.vti_Factor,
                                                                   Key .xRate = Imp.vti_Rate} Into Group
                                Select New With {
                                          Key .Dates = dateGroup,
                                          .SumAmount = Group.Sum(Function(x) x.vti_Total)}

    Thanks anyway for your permanent help in these forums.

    Sunday, December 23, 2018 8:34 PM
  • Hi Karen,

    Thanks for your example, but looks like you are using a Datatable and I need to use custom objects, in this case DxPart is a list of object that each can have several ImpFED objects.

    I have realized that my error is in this part:

    Select New With {
           Key .Dates = dateGroup,
           .SumAmount = Group.Sum(Function(x) x.ImpFED(0).vti_Total)}

    Because I am always selecting the first group (x.ImpFED(0))

    Any idea how to Sum all groups?


    G.Waters

    By understanding Grouping it's so much easier to group/sum. So to move from DataTable to custom list for instance where the following is our concrete class (we can have more properties also).

    Public Class Item
        Public Property TaxRate() As Decimal
        Public Property Value() As Decimal
        Public Property TaxAmount() As Decimal
    End Class

    This (from my first reply) will not work as Data is working against a DataRow 

    Public Class GroupData
        Public Property TaxRate As Decimal
        Public Property TaxRateSum As Decimal
        Public Property ValueSum As Decimal
        Public Property TaxAmountSum As Decimal
        Public Property Data As IGrouping(Of Decimal, DataRow)
    End Class
    

    So we adjust for Item

    Public Class GroupDataItem
        Public Property TaxRate As Decimal
        Public Property TaxRateSum As Decimal
        Public Property ValueSum As Decimal
        Public Property TaxAmountSum As Decimal
        Public Property Data As IGrouping(Of Decimal, Item)
    End Class
    

    In the following itemList is a List(Of Item) we move from DataTable to itemList and properties of Item.

    Dim results = itemList.GroupBy(
      Function(row) row.TaxRate) _
        .Select(Function(group) New GroupDataItem With
                   {
                   .TaxRate = group.Key,
                   .TaxRateSum = group.Sum(Function(row) row.TaxRate),
                   .ValueSum = group.Sum(Function(row) row.Value),
                   .TaxAmountSum = group.Sum(Function(row) row.TaxAmount),
                   .Data = group
                   })
    
    For Each group As GroupDataItem In results
        DataGridView2.Rows.Add(New Object() _
                              {
                                  group.TaxRate,
                                  group.ValueSum,
                                  group.TaxAmountSum,
                                  group.ValueSum + group.TaxAmountSum
                              })
    Next
    So with that you should be able to work your task out.  As mentioned above the key is not to simply try but instead to understand grouping/summing. Grouping and summing was around way before LINQ and Lambda :-)


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by George Waters Monday, December 24, 2018 12:54 AM
    Sunday, December 23, 2018 9:29 PM
    Moderator