Imports System

Imports System.Data

Imports System.Data.DataSetExtensions

Imports System.Linq

Imports System.Xml

Public Module Module1

    Public Sub Main()

    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)

    Console.WriteLine("result: {0}", result)                    

    End Sub

End Module