Summing multiple columns by one column in LINQ

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

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

Commented:
Yeah what Norie said.  :D

-saige-

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