VB.Net - DataTable Looping/Grouping and Getting Sum

Good Day Experts!

I have what seems to be a a straightforward little project, but I would like to check with you to see if there is a better way to do it.  My DataTable looks something like this:

Customer          Invoice          Amount
A                           123               $10
A                           546               $20
A                           111               $15
B                           999               $33
B                           888               $44
C                           765               $66
C                           777               $22

I need to loop through the DataTable and total amount based on Customer A=$45  B=$77 C=$88.
As I loop, I need to stop adding to the total amount when the Customer changes.  But, how do I dynamically create the variables to hold the total amounts? It feels like I need to run a Select Customer, Sum(Amount) on DataTable Group By Customer query on the DataTable.

Can you help me with suggestions on how to get started on achieving my end goal?

Thanks,
jimbo99999
Jimbo99999Asked:
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.

it_saigeDeveloperCommented:
We can accomplish this with linq rather easily: 
Module Module1
	ReadOnly invoices As New List(Of Object()) From _
		{ _
			New Object(2) {Convert.ToChar("A"), 123, 10.0}, _
			New Object(2) {Convert.ToChar("A"), 546, 20.0}, _
			New Object(2) {Convert.ToChar("A"), 111, 15.0}, _
			New Object(2) {Convert.ToChar("B"), 999, 33.0}, _
			New Object(2) {Convert.ToChar("B"), 888, 44.0}, _
			New Object(2) {Convert.ToChar("C"), 765, 66.0}, _
			New Object(2) {Convert.ToChar("C"), 777, 22.0} _
		}

	Sub Main()
		Dim table As New DataTable
		table.Columns.AddRange({New DataColumn("Customer", GetType(Char)), New DataColumn("Invoice", GetType(Integer)), New DataColumn("Amount", GetType(Decimal))})
		For i As Integer = 0 To invoices.Count - 1
			table.Rows.Add(invoices(i))
		Next

		Dim totals = (From [row] As DataRow In table Group [row] By Customer = row("Customer") Into g = Group Select New With {Key Customer, .Total = g.Sum(Function(x) x("Amount")).ToString("C2")})
		For Each total In totals
			Console.WriteLine(total)
		Next
		Console.ReadLine()
	End Sub
End Module

Open in new window

Produces the following output -Capture.JPG-saige-
Jimbo99999Author Commented:
Hey There...thanks for responding.

I have never used linq before.  Can I use it in VisualStudio 2005?

Thanks,
jimbo99999
it_saigeDeveloperCommented:
While I personally have never attempted this, you can use the CTP update below in order to allow LINQ to be used in Visual Studio 2005 (you also have to set the project for .NET 3.5):

https://www.microsoft.com/en-us/download/details.aspx?id=11289

But it might make more sense to import the project into a newer version of Visual Studio or use a more compatible method rather than trying to futz with your system.

Without using LINQ, you could accomplish the same results using something like: 
Module Module1
    ReadOnly invoices As New List(Of Object())

    Sub Main()
        invoices.Add(New Object(2) {Convert.ToChar("A"), 123, 10.0})
        invoices.Add(New Object(2) {Convert.ToChar("A"), 546, 20.0})
        invoices.Add(New Object(2) {Convert.ToChar("A"), 111, 15.0})
        invoices.Add(New Object(2) {Convert.ToChar("B"), 999, 33.0})
        invoices.Add(New Object(2) {Convert.ToChar("B"), 888, 44.0})
        invoices.Add(New Object(2) {Convert.ToChar("C"), 765, 66.0})
        invoices.Add(New Object(2) {Convert.ToChar("C"), 777, 22.0})

        Dim table As New DataTable
        table.Columns.AddRange(New DataColumn() {New DataColumn("Customer", GetType(Char)), New DataColumn("Invoice", GetType(Integer)), New DataColumn("Amount", GetType(Decimal))})
        For i As Integer = 0 To invoices.Count - 1
            table.Rows.Add(invoices(i))
        Next

        Dim totals() As Object = {}
        Dim Customer As Char = ""
        Dim Sum As Decimal = 0
        For Each [row] As DataRow In table.Rows
            If Customer <> [row]("Customer") Then
                If Customer <> "" AndAlso Sum <> 0 Then
                    ReDim Preserve totals(totals.Length)
                    totals(totals.Length - 1) = New Object(1) {Customer, Sum}
                End If
                Customer = [row]("Customer")
                Sum = 0
            End If
            Sum = Sum + [row]("Amount")
        Next

        If Customer <> "" Then
            ReDim Preserve totals(totals.Length)
            totals(totals.Length - 1) = New Object(1) {Customer, Sum}
        End If

        For Each total In totals
            Console.WriteLine("{{ Customer = {0}, Total = {1} }}", total(0), CType(total(1), Decimal).ToString("C2"))
        Next

        Console.ReadLine()
    End Sub
End Module

Open in new window


-saige-

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
Jimbo99999Author Commented:
Thanks for the help.  I am trying this afternoon.

jimbo99999
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.