Summing multiple columns by one column in LINQ

Dear Experts,

I have a data table in a dataset in vb.net code. the data table has four columns customerid, month,  ActiveDays, Purchase_Amt

I want to sum  ActiveDays and  Purchase_Amt by customer (total  ActiveDays and Purchase_Amt by customer)

I am thinking of using LINQ, but i have not yet gotten how to go about it.

Help

Anthony
LVL 1
Anthony MatovuBusiness Analyst, MTN UgandaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

NorieAnalyst Assistant Commented:
Anthony

Assuming CustomerID is numeric you could try something like this.
	Dim query = From row In dt
			Group row By CustomerID= row.Field(Of Int32)("CustomerID") Into CustomerGroup  = Group
        	Select New With {
            	Key CustomerID,
            	.TotalActivityDays = CustomerGroup.Sum(Function(r) r.Field(Of Int32)("ActiveDays")),
            	.TotalPurchaseAmt = CustomerGroup.Sum(Function(r) r.Field(Of Decimal)("Purchase_Amt"))
       	}

		For Each x In query
    		    Console.WriteLine("{0} {1} {2}", x.CustomerID, x.TotalActivityDays, x.TotalPurchaseAmt)
		Next x

Open in new window

it_saigeDeveloperCommented:
Like this:
Imports System.ComponentModel
Imports System.Runtime.CompilerServices

Module Module1
	Sub Main()
		Dim table = (From i In Enumerable.Range(1, 20) Select New With {.CustomerID = If(i Mod 5 = 0, 5, If(i Mod 4 = 0, 4, If(i Mod 3 = 0, 3, If(i Mod 2 = 0, 2, 1)))), .Month = 12 \ i, .ActiveDays = i * 9, .Purchase_Amt = i * 9 / 3}).ConvertToDataTable("Customers")
		table.PrintToConsole()
		Dim sums = (From row In table.AsEnumerable()
					Group row By CustomerID = row("CustomerID") Into g = Group
					Select New With {.CustomerID = CustomerID, .ActiveDays = g.Sum(Function(row) row("ActiveDays")), .Purchase_Amt = g.Sum(Function(row) row("Purchase_Amt"))})
		For Each sum In sums
			Console.WriteLine(sum)
		Next
		Console.ReadLine()
	End Sub
End Module

Module Extensions
	<Extension()>
	Sub DrawHorizontalSeparator(ByVal width As Integer, ByVal separator As Char)
		Console.WriteLine(New String(separator, width))
	End Sub

	<Extension()>
	Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T), Optional ByVal name As String = Nothing) As DataTable
		Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
		Dim table As DataTable = New DataTable(If(Not String.IsNullOrWhiteSpace(name), name, String.Empty))

		For i As Integer = 0 To properties.Count - 1
			Dim [property] As PropertyDescriptor = properties(i)
			If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
				table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
			Else
				table.Columns.Add([property].Name, [property].PropertyType)
			End If
		Next

		Dim values(properties.Count - 1) As Object
		For Each item As T In source
			For i As Integer = 0 To properties.Count - 1
				values(i) = properties(i).GetValue(item)
			Next
			table.Rows.Add(values)
		Next

		Return table
	End Function

	<Extension()>
	Public Sub PrintToConsole(ByVal table As DataTable)
		Dim width = (25 * table.Columns.Count) + table.Columns.Count
		Console.WriteLine("Table Name: {0}", table.TableName)
		width.DrawHorizontalSeparator("=")
		Console.WriteLine("|{0}|", String.Join("|", table.Columns.Cast(Of DataColumn)().Select(Function(x) String.Format("  {0}  ", x.ColumnName).PadRight(25))))
		width.DrawHorizontalSeparator("=")
		For Each row As DataRow In table.Rows
			Console.WriteLine("|{0}|", String.Join("|", row.ItemArray.Select(Function(x) String.Format("  {0}  ", x.ToString()).PadRight(25))))
		Next
		width.DrawHorizontalSeparator("-")
	End Sub
End Module

Open in new window

Which produces the following output -Capture.PNG-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
it_saigeDeveloperCommented:
Yeah what Norie said.  :D

-saige-
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
ASP.NET

From novice to tech pro — start learning today.