Get sum of required values (with filter) in datatable VB.NET

Question

0.00/5 (No votes)

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 15-Apr-18 19:16pm

Angel Deeyaz

Updated 15-Apr-18 21:16pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-04-15T21:17:00

Solution 1

Check this:

VB.NET

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

Posted 15-Apr-18 21:17pm

Maciej Los

Comments

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).

Angel Deeyaz 16-Apr-18 5:16am

I tried also in fiddle with another set of data and I think there's something wrong.

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})	


	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

The result should be 6, 600.00.

result = 1800 + 1800 + 3000

Maciej Los 16-Apr-18 5:33am

Why 6,600.00?
You wanted to skip last record. In this case last = first for room no. 102.
If you want to get 6,600.00, change your code to:

.TotalAmt = grp.Take(If(grp.Count>1,grp.Count-1, 1)).Sum(Function(x) x.Field(Of Double)("Amount")) _

Angel Deeyaz 16-Apr-18 5:50am

Should skip the last record of each group.

It works in our demo sample. However, after changing the code in my end, it sums up the amount including the last record of each Room(group).

Angel Deeyaz 16-Apr-18 6:10am

I don't know if it is because my datatable is filtered by day.

CPallini 16-Apr-18 4:11am

5.

Maciej Los 16-Apr-18 4:20am

Thank you, Carlo.

Angel Deeyaz 24-Apr-18 0:50am

I've got it few days ago. Thank you!

Maciej Los 24-Apr-18 2:05am

You're very welcome. Please, accept my answer as a solution (green button) to remove your question from unanaswered list.
Cheers,
Maciej