Solved

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

Posted on 2014-11-21
4
1,155 Views
Last Modified: 2016-02-16
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
0
Comment
Question by:FamousMortimer
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
Comment Utility
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
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
 
LVL 10

Author Closing Comment

by:FamousMortimer
Comment Utility
Brilliant sir.
0
 
LVL 10

Author Comment

by:FamousMortimer
Comment Utility
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now