SQL function
Using the SQL function, you can directly calculate the flatness of the database data directly among a SELECT statement
Mean, total number, minimum, maximum, sum, standard deviation, variation, etc. Use the Recordset object
These SQL functions can also be used.
The SQL function includes the following:
AVG function: Calculate the arithmetic average of a particular field data in the query.
Count Function: Calculate the number of records that meet the query conditions.
Min, MAX function: Remove the information that meets the first, the topmost record of the query condition in the specified field.
First, Last Function: Remove the minimum, maximum of the query conditions in the specified field.
STDEV function: Calculate the standard deviation of the query condition in the specified field.
SUM function: Calculate the sum of the data in accordance with the query criteria in the specified field.
Var, Function: Calculate the number of variation estimates that meet the query conditions in the specified field.
How does ADO use SQL functions?
Such as the ASP program rs21.asp, use the browser, browse the result of the user, display execution SQL function
the result of.
The ASP program is RS21.ASP as follows:
<%
SET CONN1 = Server.createObject ("AdoDb.Connection")
Conn1.open "DBQ =" & Server.mappath ("Ntopsamp.mdb") & "; Driver =
{Microsoft Access Driver (* .mdb)}; driverid = 25; FIL = MS Access; "
SET RS2 = Server.createObject ("AdoDb.Recordset")
SQLSTR = "SELECT AVG (Price) AS Average from Products"
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
AVG:" & RS2 ("average")
RS2.Close
SQLSTR = "SELECT COUNT" from Product "
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
count:" & rs2 (0)
RS2.Close
SQLSTR = "SELECT COUNT (*) from Product"
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
count (*):" & rs2 (0)
RS2.Close
SQLSTR = "SELECT SUM" from product "
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
SUM:" & RS2 (0)
RS2.Close
SQLSTR = "SELECT MIN" from Product "
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
MIN:" & RS2 (0)
RS2.Close
SQLSTR = "SELECT MAX (price) from product
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
Max:" & RS2 (0)
RS2.Close
SQLSTR = "SELECT First (Price) from Product" rs2.open sqlstr, conn1, 1, 1
Response.write "
first):" & rs2 (0)
RS2.Close
SQLSTR = "SELECT LAST" from product "
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
Last:" & RS2 (0)
RS2.Close
SQLSTR = "SELECT First Registration" from Product "
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
first (code):" & rs2 (0)
RS2.Close
SQLSTR = "SELECT LAST" from Product "
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
Last:" & RS2 (0)
RS2.Close
SQLSTR = "SELECT stdev (price) from product"
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
stdev:" & RS2 (0)
RS2.Close
SQLSTR = "SELECT VAR" from Product "
RS2.Open SQLSTR, CONN1, 1, 1
Response.write "
Var:" & rs2 (0)
RS2.Close
Cn1.close
%>
The SQL function is described in detail below:
AVG function
The AVG function calculates the arithmetic average of a particular field data in the query.
The syntax is AVG (arithmetic). The calculation, can be a field name, an arithmetic, or a function, this function can
To be inside or user-defined, but cannot be other SQL functions.
When the AVG function is calculated, it does not contain any information for NULL.
COUNT function
The count function calculates the number of records that meet the query conditions.
The syntax is count (an arithmetic). Operation, can be a field name, *, multiple field names, arithmetic,
Or a function, this function can be inside or user-defined, but cannot be other SQL functions.
When the count function is calculated, it does not contain any information for NULL.
However, count (*) calculates all the number of records that meet the query, including those NULL.
If the field name of Count (field name) is multiple fields, use & separation between field names.
Among the multiple fields, at least one of the values of a field is not NULL, the count function will be calculated as a
recording. If multiple fields are NULL, it is not a record. for example:
SELECT Count (Price & Code) from Products
First / Last function
The first function, the Last function, the first one, the last record, the first one, the last record of the query criteria
data of.
The syntax is First (arithmetic) and LAST. The calculation, can be a field name, an operation, or
A function, this function can be inside or user-defined, but cannot be other SQL functions.
Min / max function
The min function, the MAX function, and the minimum value of the query condition is in line with the specified field.
The syntax is MIN (arithmetic) and max (arithmetic). The operation can be a field name, an arithmetic, or a function, which can be inside or user-defined, but cannot be other SQL functions.
STDEV function
The stdev function calculates the standard deviation of the query condition in the specified field.
The syntax is stdev (an arithmetic). Operation, can be a field name, an arithmetic, or a function, this function
Can be inside or user-defined, but cannot be other SQL functions.
If the record is compliant with the query condition, the stdev function will pass a null value, which indicates
Standard difference cannot be calculated.
SUM function
The SUM function calculates the sum of the data that meets the query conditions in the specified field.
The syntax is SUM (arithmetic). The calculation, can be a field name, an arithmetic, or a function, this function can
To be inside or user-defined, but cannot be other SQL functions.
SUM functions can use two field data operations, such as calculating the unit price and quantity field of the product:
SELECT SUM (quantity * number) from Product
VAR function
The VAR function calculates the number of variation estimates that meet the query conditions in the specified field.
The syntax is VAR (arithmetic). The calculation, can be a field name, an arithmetic, or a function, this function can
To be inside or user-defined, but cannot be other SQL functions.
If the record is compliant with the query, the VAR function will pass a null value, which means
Can calculate the variation.
