RSS

Aggregate types of DataTable in C#

21 Jul

In this article we will talk about “How can you do sum calculations on your DataTable like SSMS?”, in other words how can you implement aggregate functions/types (SUM, MIN, MAX .. etc.) on your DataTable instance.

Compute Feature of DataTable

Computes the given expression on the current rows that pass the filter criteria.

Parameters

  • expression
    • Type: System.String
    • The expression to compute.
  • filter
    • Type: System.String
    • The filter to limit the rows that evaluate in the expression.

Return Value

  • Type: System.Object
  • An Object, set to the result of the computation. If the expression evaluates to null, the return value will be Value.

What does this method do?

It computes the given expression on the current rows that pass the filter criteria.

object DataTable.Compute(string expression, string filter)

as you see, the function return “Object”,so you have to cast it to your data type.

The following aggregate types are supported in expression:

      • Sum (Sum)
      • Avg (Average)
      • Min (Minimum)
      • Max (Maximum)
      • Count (Count)
      • StDev (Statistical standard deviation)
      • Var (Statistical variance)

If you have the following DataTable for customers in a company, how to get the minimum customer ID or the maximum one, minimum or maximum date of birth, count of males or females .. etc.??

01

If you are on SSMS, this will be so easy to do, but on C# or DataTable specifically you will face some problems. Here, the Compute() method of DataTable will do all of this, let’s see how:

// Get the minimum customer id in the table
dtObject.Compute("MIN(CustomerKey)", string.Empty);

// Get the maximum customer id in the table
dtObject.Compute("MAX(CustomerKey)", string.Empty);

// Get the birth date of customers
dtObject.Compute("MIN(BirthDate)", string.Empty);

// Get the birth date of customers
dtObject.Compute("MAX(BirthDate)", string.Empty);

// Get rows count of males (only)
dtObject.Compute("COUNT(CustomerKey)", "Gender='M'")

// Get average (avg.) of yearly income of females.
dtObject.Compute("AVG(YearlyIncome)", "Gender='F'");

// Get count of customers whose date of birth is 
// greater than Dec 22, 1970 (MM/dd/yyyy)
dtObject.Compute("COUNT(CustomerKey)", "BirthDate > #12/22/1970#");

// Get statistical variance of YearlyIncome
dtObject.Compute("VAR(YearlyIncome)", "");

// Get Statistical standard deviation of YearlyIncome
dtObject.Compute("StDev(YearlyIncome)", "");
 
1 Comment

Posted by on July 21, 2013 in Visual C# . NET

 

Tags: , , , , , , , , , , , , , ,

One response to “Aggregate types of DataTable in C#

  1. john

    November 19, 2015 at 10:53 PM

    Thanks much

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: