VB.Net - DataTable Looping/Grouping and Getting Sum

Jimbo99999
Jimbo99999 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
it_saigeDeveloper

Commented:
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-
Jimbo99999

Author

Commented:
Hey There...thanks for responding.

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

Thanks,
jimbo99999
Developer
Commented:
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-
Jimbo99999

Author

Commented:
Thanks for the help.  I am trying this afternoon.

jimbo99999

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial