Data | Statistics for data records in statistical DataTable
When we use SQL Server these databases, we can easily count the results by sum, aver, count, and so on, in the dataset (DataTable) where the data has been retrieved? In particular, the dataset was obtained through Web service, but there was no way to change the SELECT statement to get the statistics. So is it possible to make statistics in the dataset/datatable? The answer is yes.
In MSDN, there is a MS recommended statistical method, which is to sum the data line by row, which is actually equal to none (perhaps this method is only for the DataGrid to get subtotals), Because this method uses the DataGrid ItemDataBind event to accumulate the data, it is no different from our manual write code statistics.
This article introduces a simple method that can easily obtain the statistical results of records in a DataTable without the need for an itemized calculation. The simple approach is to invoke the function compute of a powerful DataTable.
The description of the call (in C # as an example, hereinafter):
public Object Compute (String strexpression,string strfilter)
Parameters:
Strexpression: The expression string to evaluate, essentially similar to the statistical expression in SQL Server
Strfilter: Statistic filter string, only records that satisfy this filter condition will be counted
Second, the invocation example:
The following example assumes a product sales table that describes the actual record of sales of each salesperson in a marketplace, including fields: Name, Gender (sex,0 is female, 1 male), birthday (birthday), code for selling products (proid), Number of sales (Quantity), Sales price (prices).
1. Statistics of the number of salespeople for women of all sexes:
Table.compute ("Count (*)", "sex=0");
2. Statistics of all salespeople older than 20 years of age
Table.compute ("Count (*)", "birthday< '" +today);//today for today's date string
3. Statistics on average prices of products sold
Table.compute ("aver (Price)", "true");
4. Sales quantity of Product Code 1:
Table.compute ("Sum (Quantity)", "proid=1");
5. Total sales amount for all products:
To count the total sales amount, we can get it by quantity*price because there is no amount data in the table for a sales promoter. Like what:
Table.compute ("Sum (Quantity*price)", "true");
One problem here is that the statistical function of a DataTable is not as strong as SQL Server, and this statistic is wrong because compute statistics do not have the function of data such as SUM (Quantity*price). What about it?
For the statistics of such complex data, we can create a new field in the DataTable to complete, such as amount, and set the field expression to quantity*price so that we can use the statistics function:
Table.compute ("Sum (Amount)", "true");
