Record statistics in datatable

When using SQL Server databases, we can easily calculate relevant results through sum, aver, count, and so on. What about dataset (datatable) that has retrieved data? In particular, dataset is obtained through the web service. At this time, there is no way to modify the SELECT statement to obtain these statistics. Can statistics be performed in dataset/datatable? The answer is yes.

In msdn, there is a statistical method recommended by MS, which is to calculate the sum of Data row by row, in fact, there is equal to none (maybe this method is only used for obtaining the subtotal for the DataGrid), because this method uses the itemdatabind event of the DataGrid to accumulate the data, and we manually writeCodeThere is no difference in statistics.

This article introduces a simple method. You can easily obtain the record statistics in the datatable without having to calculate records one by one. This simple method is to call the powerful datatable function compute.

1. Call description (C # is used as an example ):

Public object compute (string strexpression, string strfilter)

Parameters:

Strexpression: expression string to be calculated. It is basically similar to the statistical expression in SQL Server.

Strfilter: A Statistical filter string. Only records that meet this filter condition are counted.

Ii. Call example:

In the following example, assume a product sales table that describes the actual sales records of various promoters in a mall. The fields include name, sex, and 0, 1 is male, birthday, proid, quantity, and price ).

1. Count the number of salespersons whose gender is female:
Table. Compute ("count (*)", "Sex = 0 ");

2. Count all sales staff older than 20
Table. Compute ("count (*)", "birthday <'" + today); // today is the date string of today.

3. Measure the average prices of products sold.
Table. Compute ("aver (price)", "true ");

4. Count the number of products sold with the product code 1:
Table. Compute ("sum (Quantity)", "proid = 1 ");

5. Total sales of all products:
To calculate the total sales amount, because the table does not contain the sales amount of a certain promoter of a product, we can obtain it through quantity * price. For example:
Table. Compute ("sum (quantity * price)", "true ");

Here is a problem: datatable does not have a strong statistical function in sqlserver, which is incorrect because compute does not have the data function such as sum (quantity * price. What should we do?

For Statistics of such complex data, we can create a new field in the datatable, such as amount, and set the expression of this field to quantity * price, in this way, we can use the statistical function:
Table. Compute ("sum (amount)", "true ");