Saturday, June 21, 2008

Performing Aggregate Functions (MAX, MIN, SUM, COUNT) on DataTable

In this article we will discuss:
  • How to get Maximum (MAX) or Minimum (MIN) value from DataTable
  • How to SUM all the values of a column in DataTable
  • How to Count all the values of a column in DataTable
Level: Beginner

Knowledge Required:

We have used DataTable Class of .net framework lots of time to load the data from Physical Database. DataTable NOT only stores the Data but can also perform other things like performing calculation on a column, e.g. getting Max./Min. item, counting, etc. So in this article we will discuss how can we perform this type of calculation on DataTable.

DataTable has a public method called Compute. We can use the aggregate functions in this method as,

Dim iMaxRow_ID As Integer
iMaxRow_ID = StudentTable.Compute("MAX(Row_ID)", "")

The above example will get the Maximum Row_ID (Row_ID is a column in StudentTable) from StudentTable.

Dim iTotalOrders As Integer
iTotalOrders = OrderTable.Compute("Count(Order_ID)", "Create_Date > #15-Jan-2008#")

Above example will count number of Orders that were created after 15-Jan-2008

For detailed information:

MSDN DataTable.Compute Method

No comments: