VB.NET Group and Sum Custom List(of T) LINQ

Hi,

i have a class 'CustomerSales' below and two lists...

Private _customerSalesList As List(Of CustomerSales)
Private _priorYearSalesList As List(Of PriorSales)

Public Class CustomerSales
    Implements IComparable(Of CustomerSales)
    Public Sub New(ByVal customerNumber As Int32,
                   ByVal groupCode As String,
                   ByVal mtdSales As Integer,
                   ByVal mtdBudget As Integer,
                   ByVal qtdSales As Integer,
                   ByVal qtdBudget As Integer,
                   ByVal ytdSales As Integer,
                   ByVal ytdBudget As Integer)
        Me.CustomerNumber = customerNumber
        Me.GroupCode = groupCode
        Me.MtdSales = mtdSales
        Me.MtdBudget = mtdBudget
        Me.QtdSales = qtdSales
        Me.QtdBudget = qtdBudget
        Me.YtdSales = ytdSales
        Me.YtdBudget = ytdBudget
    End Sub

    Public Property CustomerNumber As Int32
    Public Property GroupCode As String
    Public Property MtdSales As Int32
    Public Property MtdBudget As Int32
    Public Property QtdSales As Int32
    Public Property QtdBudget As Int32
    Public Property YtdSales As Int32
    Public Property YtdBudget As Int32

    Public Function CompareTo(ByVal other As CustomerSales) As Integer Implements IComparable(Of CustomerSales).CompareTo
        Return -YtdSales.CompareTo(other.YtdSales)
    End Function
End Class

Open in new window


Basically I need to group by GroupCode (discarding CustomerNumber of course) and Sum the rest of the fields.  Then I need to join another list on GroupCode and add the PriorMTD/QTD/YTDSales as properties to the new object.

Public Class PriorSales
    Public Sub New(ByVal organizationalCode As String, ByVal priorMtdSales As Integer, ByVal priorQtdSales As Integer, ByVal priorYtdSales As Integer)
        Me.GroupCode = organizationalCode
        Me.PriorMtdSales = priorMtdSales
        Me.PriorQtdSales = priorQtdSales
        Me.PriorYtdSales = priorYtdSales
    End Sub

    Public Property GroupCode As String
    Public Property PriorMtdSales As Int32
    Public Property PriorQtdSales As Int32
    Public Property PriorYtdSales As Int32
End Class

Open in new window


Thanks
LVL 10
FamousMortimerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
I always forget the exact syntax for grouping in VB.NET, but I believe it should be along the lines of:

Dim query = From customerSale In customerSalesCollection
            Group customerSale By customerSale.GroupCode Into Group
            Join priorSale In priorSalesCollection On GroupCode Equals priorSale.GroupCode
            Select New With
            {
                .MtdSalesTotal = Group.Sum(Function (item) item.MtdSales),
                .MtdBudgetTotal = Group.Sum(Function (item) item.MtdBudget),
                .QtdSalesTotal = Group.Sum(Function (item) item.QtdSales),
                .QtdBudgetTotal = Group.Sum(Function (item) item.QtdBudget),
                .YtdSalesTotal = Group.Sum(Function (item) item.YtdSales),
                .YtdBudgetTotal = Group.Sum(Function (item) item.YtdBudget),
                .PriorMtdSales = priorSale.PriorMtdSales,
                .PriorQtdSales = priorSale.PriorQtdSales,
                .PriorYtdSales = priorSale.PriorYtdSales
            }

Open in new window


** edited to correct a few bugs
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
Hi FamousMortimer;

See if this is what you are looking for.

Dim custSalesList = (From cl As CustomerSales In _customerSalesList
                     Group By Code = cl.GroupCode Into clGroup = Group
                     Select New PriorSales(
                         organizationalCode:=Code,
                         priorMtdSales:=clGroup.Sum(Function(mtd) mtd.MtdSales),
                         priorQtdSales:=clGroup.Sum(Function(qtd) qtd.QtdSales),
                         priorYtdSales:=clGroup.Sum(Function(ytd) ytd.YtdSales)
                     )).ToList()

''  priorYearList contains those values that have the same GroupCode in both list with the new values for them
Dim priorYearList = (From pl As PriorSales In _priorYearSalesList
                     Join code In custSalesList On pl.GroupCode Equals code.GroupCode
                     Select New PriorSales(
                         organizationalCode:=pl.GroupCode,
                         priorMtdSales:=pl.PriorMtdSales + code.PriorMtdSales,
                         priorQtdSales:=pl.PriorQtdSales + code.PriorQtdSales,
                         priorYtdSales:=pl.PriorYtdSales + code.PriorYtdSales
                    )).ToList()

Open in new window

0
FamousMortimerAuthor Commented:
Brilliant sir.
0
FamousMortimerAuthor Commented:
Sorry Fernando... i didn't get a chance to see your comment until after i verified that kaufmed's worked for me and assigned the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.