Check this:
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange({New DataColumn("Day", GetType(Int32)), _
New DataColumn("Date", GetType(DateTime)), _
New DataColumn("Room No.", GetType(Int32)), _
New DataColumn("Room Rate", GetType(Double)), _
New DataColumn("No. of Person", GetType(Int32)), _
New DataColumn("Amount", GetType(Double))})
dt.Rows.Add(New Object(){1, New DateTime(2018, 4, 9), 101, 900.00, 2, 1800.00})
dt.Rows.Add(New Object(){2, New DateTime(2018, 4, 10), 101, 900.00, 2, 1800.00})
dt.Rows.Add(New Object(){3, New DateTime(2018, 4, 10), 101, 900.00, 2, 1800.00})
dt.Rows.Add(New Object(){1, New DateTime(2018, 4, 9), 102, 1000.00, 3, 3000.00})
dt.Rows.Add(New Object(){2, New DateTime(2018, 4, 10), 102, 1000.00, 3, 3000.00})
dt.Rows.Add(New Object(){3, New DateTime(2018, 4, 10), 102, 1000.00, 3, 3000.00})
Dim result = dt.AsEnumerable() _
.GroupBy(Function(x) x.Field(Of Int32)("Room No.")) _
.Select(Function(grp) New With _
{ _
.RoomNo = grp.Key, _
.TotalAmt = grp.Take(grp.Count-1).Sum(Function(x) x.Field(Of Double)("Amount")) _
}) _
.Sum(Function(x) x.TotalAmt)
Linq[
^] makes life easier :) See:
Introduction to LINQ in Visual Basic | Microsoft Docs[
^]
DataTableExtensions.AsEnumerable Method (DataTable) (System.Data)[
^]
Few words of explanation:
Dim result = dt.AsEnumerable() _
.GroupBy(Function(x) x.Field(Of Int32)("Room No.")) _
.Select(Function(grp) New With _
{ _
.RoomNo = grp.Key, _
.TotalAmt = grp.Take(grp.Count-1)
.Sum(Function(x) x.Field(Of Double)("Amount")) _
}) _
.Sum(Function(x) x.TotalAmt)
Enumerable.GroupBy(TSource, TKey, TElement) Method (IEnumerable(TSource), Func(TSource, TKey), Func(TSource, TElement)) (System.Linq)[
^]
Enumerable.Count(TSource) Method (IEnumerable(TSource), Func(TSource, Boolean)) (System.Linq)[
^]
Enumerable.Take(TSource) Method (IEnumerable(TSource), Int32) (System.Linq)[
^]
Enumerable.Sum(TSource) Method (IEnumerable(TSource), Func(TSource, Double)) (System.Linq)[
^]