In this article I will explain with examples, how to calculate Sum (Total) of DataTable Columns using C# and VB.Net.
This article explains how to calculate Sum (Total) of DataTable Columns using the following two methods.
1. DataTable Compute function.
2. Using LINQ.
DataTable
The following DataTable contains Salary information of Employees. This DataTable will be used to illustrate how to calculate Sum (Total) of DataTable Columns using DataTable Compute function and LINQ.
C#
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("EmployeeId", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Salary", typeof(int)) });
dt.Rows.Add(1, "John Hammond", 45000);
dt.Rows.Add(2, "Mudassar Khan", 32000);
dt.Rows.Add(3, "Robert Schidner", 19000);
dt.Rows.Add(3, "Suzanne Mathews", 18500);
VB.Net
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(2) {New DataColumn("EmployeeId", GetType(Integer)), _
New DataColumn("Name", GetType(String)), _
New DataColumn("Salary", GetType(Integer))})
dt.Rows.Add(1, "John Hammond", 45000)
dt.Rows.Add(2, "Mudassar Khan", 32000)
dt.Rows.Add(3, "Robert Schidner", 19000)
dt.Rows.Add(3, "Suzanne Mathews", 18500)
Calculate Sum (Total) of DataTable Columns using DataTable Compute function
The DataTable Compute function accepts two parameters
1. Expression – It is an Aggregate function such as SUM, COUNT, MIN, MAX and AVG.
2. Filter – It is used to filter rows like WHERE clause. If set blank then all rows are considered.
DataTable Compute without Filter expression
In the below example, Sum (Total) of Salary column of all employees is calculated using DataTable Compute function.
The Filter Expression is set Blank in order to calculate Sum (Total) of Salary column of all rows.
C#
int sum = Convert.ToInt32(dt.Compute("SUM(Salary)", string.Empty));
VB.Net
Dim sum As Integer = Convert.ToInt32(dt.Compute("SUM(Salary)", String.Empty))
DataTable Compute with Filter expression
In the below example, Sum (Total) of Salary column of employees whose ID is greater than 2 is calculated using DataTable Compute function along with Filter expression.
C#
int sum = Convert.ToInt32(dt.Compute("SUM(Salary)", "EmployeeId > 2"));
VB.Net
Dim sum = Convert.ToInt32(dt.Compute("SUM(Salary)", "EmployeeId > 2"))
Calculate Sum (Total) of DataTable Columns using LINQ
LINQ allows us to easily calculate Sum (Total) for all rows as well as some specific rows based on some condition.
LINQ calculate sum of all rows
In the below example, Sum (Total) of Salary column of all employees is calculated using LINQ.
C#
int sum = dt.AsEnumerable().Sum(row => row.Field<int>("Salary"));
VB.Net
Dim sum As Integer = dt.AsEnumerable().Sum(Function(row) row.Field(Of Integer)("Salary"))
LINQ calculate sum of specific rows
In the below example, Sum (Total) of Salary column of employees whose ID is greater than 2 is calculated using LINQ.
C#
int sum = dt.AsEnumerable().Where(row => row.Field<int>("EmployeeId") > 2).Sum(row => row.Field<int>("Salary"));
VB.Net
Dim sum As Integer = dt.AsEnumerable().Where(Function(row) row.Field(Of Integer)("EmployeeId") > 2).Sum(Function(row) row.Field(Of Integer)("Salary"))
