How does ado use SQL functions? _ MySQL

How does ado use SQL functions? SQL functions

Using SQL functions, you can directly calculate the level of database data in a SELECT statement query.
Average, total, minimum, maximum, sum, standard deviation, variation, and other statistics. Use Recordset object
You can also use these SQL functions.

SQL functions include:

Avg function: calculates the arithmetic mean value of a specific field in a query.

Count function: calculates the number of records that meet the query conditions.

Min and Max functions: return the first and last Records in the specified field that meet the query conditions.

First and Last functions: return the minimum and maximum values that meet the query conditions in a specified field.

StDev function: calculates the standard deviation of a specified field that meets the query conditions.

Sum function: calculates the Sum of data that meets the query conditions in a specified field.

Var, function: calculates the estimated number of variations in a specified field that meet the query conditions.

How does ADO use SQL functions?

For example, the ASP code rs21.ASP allows you to use a browser on the client to view the execution results and display the execution SQL functions.
.

The ASP code rs21.ASP is 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 product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Avg:" & rs2 ("average ")

Rs2.Close

SqlStr = "SELECT Count (price) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Count (price):" & rs2 (0)

Rs2.Close

SqlStr = "SELECT Count (*) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Count (*):" & rs2 (0)

Rs2.Close

SqlStr = "SELECT Sum (price) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Sum:" & rs2 (0)

Rs2.Close

SqlStr = "SELECT Min (price) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Min:" & rs2 (0)

Rs2.Close

SqlStr = "SELECT Max (price) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Max:" & rs2 (0)

Rs2.Close

SqlStr = "SELECT First (price) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> First (price):" & rs2 (0)

Rs2.Close

SqlStr = "SELECT Last (price) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Last (price):" & rs2 (0)

Rs2.Close

SqlStr = "SELECT First (code) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> First (code):" & rs2 (0)

Rs2.Close

SqlStr = "SELECT Last (code) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Last (code):" & rs2 (0)

Rs2.Close

SqlStr = "SELECT StDev (price) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> StDev:" & rs2 (0)

Rs2.Close

SqlStr = "SELECT Var (price) From product"

Rs2.Open SqlStr, conn1, 1, 1

Response. Write "<BR> Var:" & rs2 (0)

Rs2.Close

Conn1.Close

%>

SQL functions are described as follows:

Avg functions

The Avg function is used to calculate the arithmetic average value of a specific field in a query.

The syntax is Avg ). Operation type. it can be a field name, Operation type, or a function. This function can
It is an internal or user-defined function, but cannot be another SQL function.

The Avg function does not include any Null data during computation.

Count function

The Count function is used to calculate the number of records that meet the query conditions.

The syntax is Count ). Calculation Formula: Field name, *, multiple field names, calculation formula,
Or a function, which can be an internal or user-defined function, but cannot be another SQL function.

The Count function does not include any data whose value is Null.

However, Count (*) calculates the number of all records that meet the query conditions, including Null data.

If the field name of Count (field name) is called multiple fields, use & to separate them.
If at least one of the fields is not Null, the Count function is calculated as one
Record. If multiple fields are Null, it is not a record. For example:

SELECT Count (Price & code) From product

First/Last function

The First and Last functions return the First and Last Records in the specified field that meet the query conditions.
.

The syntax is "First" and "Last ). Calculation formula, which can be the field name, calculation formula, or
A function, which can be an internal or user-defined function, but cannot be another SQL function.

Min/Max functions

The Min and Max functions return the minimum and maximum values that meet the query conditions in the specified field.

The syntax is Min and Max ). Calculation formula, which can be the field name, calculation formula, or
Function, which can be an internal or user-defined function, but cannot be another SQL function.

StDev function

The StDev function calculates the standard deviation of a specified field that meets the query conditions.

The syntax is StDev ). Calculation formula. it can be a field name, calculation formula, or a function. this function
It can be an internal or user-defined function, but cannot be another SQL function.

If the following two records meet the query conditions, the StDev function returns a Null value.
The standard deviation cannot be calculated.

Sum function

Calculates the Sum of data that meets the query conditions in a specified field.

The syntax is Sum ). Operation type. it can be a field name, Operation type, or a function. This function can
It is an internal or user-defined function, but cannot be another SQL function.

The Sum function can use two data calculation methods, such as calculating the unit price and total number of fields of a product:

SELECT Sum (unit price * quantity) FROM product

Var function

The Var function is used to calculate the estimated number of variations in a specified field that meet the query conditions.

The syntax is Var ). Operation type. it can be a field name, Operation type, or a function. This function can
It is an internal or user-defined function, but cannot be another SQL function.

If the following two records meet the query conditions, the Var function returns a Null value, which indicates no
Ability to calculate the number of variations