15,742,477 members
See more:
```I have a datatable with the following fields:

Day
Date
Room Rate
No of Person
Amount
The data is as follows:

<pre>
Day Date    Room No.    Room Rate   No. of Person       Amount
1   4/9/2018    101         900.00      2           1, 800.00
2   4/10/2018   101         900.00      2           1, 800.00
3   4/10/2018   101         900.00      2           1, 800.00
1   4/9/2018    102         1000.00     3           3, 000.00
2   4/10/2018   102         1000.00     3           3, 000.00
3   4/10/2018   102         1000.00     3           3, 000.00```

I would like to get the total amount by getting the sum of Amount. But, the last day for each Room should not be included. With the above example, the total amount would be 9, 600.00 since Room 101 and Room 102 of day 3 is not included.

I tried to use the datatable compute function, but this will not be effective:

`Convert.ToInt32(DataSet.Tables("dt_Lodging").Compute("SUM(Amount)", "Day = 3") `

Day will not be limited to 3. If we have days 1 to 5, day 5 is the one which will not be included in Total.

Some suggested to compute it using the formula Room Rate * Number of nights (instead of number of days. But I have to display record for rooms from the very first day til the last day of staying in a specified room.

What I have tried:

`Convert.ToInt32(DataSet.Tables("dt_Lodging").Compute("SUM(Amount)", "Day = 3") `
Posted
Updated 15-Apr-18 21:16pm
v2

## Solution 1

Check this:

VB.NET
```Dim dt As DataTable = New DataTable()
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) '9600 ;)```

Linq[^] makes life easier :) See: Introduction to LINQ in Visual Basic | Microsoft Docs[^]
DataTableExtensions.AsEnumerable Method (DataTable) (System.Data)[^]

Few words of explanation:
VB.NET
```Dim result = dt.AsEnumerable() _ 'Returns an IEnumerable<T> object, where the generic parameter T is DataRow
.GroupBy(Function(x) x.Field(Of Int32)("Room No.")) _ 'group data by [Room No.]
.Select(Function(grp) New With _ 'select
{ _
.RoomNo = grp.Key, _ '[Room No.]
.TotalAmt = grp.Take(grp.Count-1) 'skip last row!!!
.Sum(Function(x) x.Field(Of Double)("Amount")) _ 'get sum of amount for each room
}) _
.Sum(Function(x) x.TotalAmt) 'get total amount```

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)[^]

Angel Deeyaz 16-Apr-18 3:29am
I tried it and its result is 0.

` Dim groupedTable = From objtbl In BookingDataSet.Tables("dt_Lodging").AsEnumerable()                        Group objtbl By key = objtbl.Field(Of String)("RoomNo") Into Group                       Select RoomNo = key, _                      amount = Group.Sum(Function(objtbl) objtbl("Amount")) `

What I am trying to do with is to group RoomNo, and do not include last record for each group. The above code just grouped and get the sum including the last row of each group.

I am new in Linq but I am trying to leanr and understand it. :)
Maciej Los 16-Apr-18 3:53am
I tried my solution and it's working fine.
Angel Deeyaz 16-Apr-18 3:36am
The problem is with
` .TotalAmt = grp.Take(grp.Count-1)  `
.

An error also occured if one row is deleted.
Maciej Los 16-Apr-18 3:55am
I don't understand what you mean. Have you tried my solution (based on your example)?

I've created .net fiddle: here
Angel Deeyaz 16-Apr-18 5:01am
I tried your solution, yes it works. But when it tried it with my own,
total amount is zero. When i removed " -1" in
" .TotalAmt = grp.Take(grp.Count-1) ", it display the overall total amount (including the last row of each group).