Group and Sum columns in a Datatable

Help Activities
, ,
1

Hi.

I am trying to Group and Sum some columns in a datatable and I have tried numerous ways to do this including LINQ, Dictionaries and other loops with no success. Wondering if someone smarter than me can point out a simply way to do it. This is what I have.

dtAttendeeList - This is a list of Attendees who attended the training.
dtAttendeeDetailed - This is a list of each attendee, the training session they attended, and the topics within the session and hours. Hours are recorded as Double (e.g 1.25) An attendee can attended multiple sessions so there are multiple records hence wanting to sum each row. The Topic hours can be blank or have a number. Not all sessions have time recorded for each topic. Everybody attending the same session will have the same hours.

format of dtAttendeeDetailed
Attendee Name, Session Name, Topic1Hours, Topic2Hours, Topic3Hours, Topic4Hours, Topic5Hours

example data:
Joe Blogs, Session 1, 1.0, 0, 0, 0, 0
Joe Blogs, Session 2, 0.25, 1.5, 0, 0, 1
Jill Blogs, Session 1, 1.0, 0, 0, 0, 0

I have a new Datatable created (dtAttendeeSummary) with the structure:

Attendee Name, Topic1Hours, Topic2Hours, Topic3Hours, Topic4Hours, Topic5Hours

This data will have been grouped by each Attendee and them sum each TopicHours column

end result for Joe Blogs would be:

Joe Blogs, 1.25, 1.5, 0, 0, 1

I want to loop through the dtAttendeeList and get the Attendee Name, then use that to match records in dtAttendeeDetailed, or if the data in dtAttendeeDetailed is simply grouped then I’ll get the results for each Attendee separately and use it for further processes.

Thank you in advance.

Forum Engagement Daily Reports
2

hi, @craig.norton group dtAttendeeDetailed by Attendee Name, then sum each topic column for that attendee, try this if it works for you

(From row In dtAttendeeDetailed.AsEnumerable Group row By Attendee = row(“Attendee Name”).ToString Into grp = Group Let topic1 = grp.Sum(Function(r) If(String.IsNullOrWhiteSpace(r(“Topic1Hours”).ToString), 0, CDbl(r(“Topic1Hours”)))) Let topic2 = grp.Sum(Function(r) If(String.IsNullOrWhiteSpace(r(“Topic2Hours”).ToString), 0, CDbl(r(“Topic2Hours”)))) Let topic3 = grp.Sum(Function(r) If(String.IsNullOrWhiteSpace(r(“Topic3Hours”).ToString), 0, CDbl(r(“Topic3Hours”)))) Let topic4 = grp.Sum(Function(r) If(String.IsNullOrWhiteSpace(r(“Topic4Hours”).ToString), 0, CDbl(r(“Topic4Hours”)))) Let topic5 = grp.Sum(Function(r) If(String.IsNullOrWhiteSpace(r(“Topic5Hours”).ToString), 0, CDbl(r(“Topic5Hours”)))) Select dtAttendeeSummary.Rows.Add({Attendee, topic1, topic2, topic3, topic4, topic5}) ).CopyToDataTable

1 Like
Forum Engagement Daily Reports
3

Hello @craig.norton Try these linqs

1st for static column name
dtSummary = (From row In dtAttendeeDetailed.AsEnumerable() Group row By Attendee = row(“Attendee Name”).ToString() Into g = Group Select dtAttendeeDetailed.Clone().LoadDataRow(({Attendee}.Concat(Enumerable.Range(1,5).Select(Function(i) g.Sum(Function(r) If(IsDBNull(r(“Topic” & i & “Hours”)), 0, CDbl(r(“Topic” & i & “Hours”)))))).ToArray()), False)).CopyToDataTable()

2nd for dynamic column which contains Topic word

dtSummary = (From row In dtAttendeeDetailed.AsEnumerable() Group row By Attendee = row(“Attendee Name”).ToString() Into g = Group Let topicCols = dtAttendeeDetailed.Columns.Cast(Of DataColumn)().Where(Function(c) c.ColumnName.StartsWith(“Topic”)).Select(Function(c) g.Sum(Function(r) If(IsDBNull(r(c)), 0, CDbl(r(c))))) Select dtAttendeeDetailed.Clone().LoadDataRow(({Attendee}.Concat(topicCols).ToArray()), False)).CopyToDataTable()

Cheers

4

This worked perfectly. Thank you so much !!!

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.