How to use the average, total, minimum, and maximum values of SQL functions

Using SQL functions, you can directly calculate the average, total, minimum, maximum, sum, standard deviation, and variance statistics of database data in a query of a SELECT statement. You can also use these SQL functions when using a Recordset object.

The SQL functions include the following:

Avg Function: Calculates the arithmetic mean of a particular field data in a query.

Count function: Calculates the number of records that meet the query criteria.

Min, max function: Returns the data from the first and last record in the specified field that meet the query criteria.

First, last function: Returns the minimum and maximum values that match the query criteria in the specified field.

Stdev function: Calculates the standard deviation in the specified field that meets the query criteria.

Sum function: Calculates the sum of the data in the specified field that matches the query criteria.

VAR, function: Calculates the estimate of the variance in the specified field that matches the query criteria.

How does ADO use SQL functions?

It is described in detail as follows: The AVG function calculates the arithmetic mean of a particular field in a query.

The syntax is AVG (expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.

The AVG function, when evaluated, does not contain any data that has a value of NULL.

Count function

Count function to calculate the number of record bars that meet the query criteria.

The syntax is count (expression). An equation can be a field name, *, multiple field names, expressions, or a function that can be an internal or consumer defined, but not another SQL function.

The Count function does not contain any data that has a value of NULL when it is evaluated.

However, COUNT (*) calculates all the number of record bars that meet the query criteria, including those that are null.

If the field name of Count (field name) is multiple fields, use & separate the field names.

In cases where at least one field has a value that is not NULL, the Count function is evaluated as a record. If more than one field is null, it is not considered a record. Such as:

Select COUNT (Price & code) from product

First/last function

The first function, the last function, returns the data in the specified field that meets the query criteria, and the last record.

The syntax is first (expression) and last (expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.

Min/max function

The Min function, the Max function, returns the minimum and maximum values that match the query criteria in the specified field.

The syntax is min (expression) and max (expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.

Stdev function

The STDEV function calculates the standard deviation in the specified field that matches the query criteria.

The syntax is Stdev (an expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.

If the record that meets the query criteria is two or less, the STDEV function returns a null value that indicates that the standard deviation cannot be computed.

Sum function

The SUM function calculates the sum of the data in the specified field that matches the query criteria.

The syntax is sum (an expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.

The SUM function can use two field data operations, such as calculating the total price and quantity fields of a product:

Select SUM (unit price * quantity) from product

var function

The Var function calculates the estimated number of variants in the specified field that match the query criteria.

The syntax is var (expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.

If the record that meets the query criteria is two or less, the VAR function returns a null value, which indicates that the variance number cannot be computed.

Select min (c) from

(select min (b) as C from Tab_name

Union

Select min (a) as C from Tab_name)

Two tables are known

Department Table Department: Department number dept_id, department name Dept_name

Employee Table Employee: Employee Number emp_id, employee name Emp_name, department number dept_id, salary emp_wage

1) to seek the average salary of each department

1. Select D.dept_name, round (avg (NVL (e.emp_wage,0)), 2) Sal from employee e left join Department D on E.de  pt_id=d.dept_id GROUP by D.dept_name;

2) to seek the total wages of each department

1. Select D.dept_name,sum (NVL (e.emp_wage,0)) Sal from employee e left join Department D on E.DEPT_ID=D.D  ept_id GROUP by D.dept_name;

3) The department name, employee ID, and employee salary that the employee who asks for a salary greater than 5000 belongs to

1. Select D.dept_name,e.emp_id,e.emp_wage from Department D, employee e where e.emp_wage>5000 and D.dept_  id = e.dept_id;

involves function interpretation:

 Sum is the average function that will require a column sum (column name) for the sum value

Avg for averaging function, will require the average of the column AVG (column name)

NVL a function that has null values for other data if not empty, NVL (an empty column, other values that place null values)

Round is the rounding function, round (column name, number of decimal digits reserved)

Oracle Common functions: Average, total, minimum, maximum, sum, standard deviation

Avg Function: Calculates the arithmetic mean of a particular field data in a query. Count function: Calculates the number of records that meet the query criteria. Min, max function: Returns the data from the first and last record in the specified field that meet the query criteria. First, last function: Returns the minimum and maximum values that match the query criteria in the specified field. Stdev function: Calculates the standard deviation in the specified field that meets the query criteria. Sum function: Calculates the sum of the data in the specified field that matches the query criteria. VAR, function: Calculates the estimate of the variance in the specified field that matches the query criteria.　　It is described in detail as follows: The AVG function calculates the arithmetic mean of a particular field in a query. The syntax is AVG (expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function. The AVG function, when evaluated, does not contain any data that has a value of NULL.　　The Count function counts the number of record bars that meet the query criteria. The syntax is count (expression). An equation can be a field name, *, multiple field names, expressions, or a function that can be an internal or consumer defined, but not another SQL function. The Count function does not contain any data that has a value of NULL when it is evaluated.　　However, COUNT (*) calculates all the number of record bars that meet the query criteria, including those that are null. If the field name of Count (field name) is multiple fields, use & separate the field names. In cases where at least one field has a value that is not NULL, the Count function is evaluated as a record. If more than one field is null, it is not considered a record.　　For example: SELECT COUNT (Price & code) from product First/last function first function, last function, return to the specified field in accordance with the criteria of the query, the last record of the data. The syntax is first (expression) and last (expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.　　The Min/max function min function, max function, returns the minimum and maximum values that match the query criteria in the specified field. The syntax is min (expression) and max (expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.　　The Stdev function Stdev function that calculates the standard deviation of the specified field that meets the query criteria. The syntax is Stdev (an expression). An equation can be a field name, an expression, or a function that canis an internal or user-defined, but cannot be an additional SQL function. If the record that meets the query criteria is two or less, the STDEV function returns a null value that indicates that the standard deviation cannot be computed.　　Sum function Sum function to calculate the sum of the data in the specified field that matches the query criteria. The syntax is sum (an expression). An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function.　　The SUM function can use two field data operations, such as calculating the total price and quantity fields of a product: select SUM (unit price * quantity) from the product var function var function, calculates the estimated number of variants in the specified field that match the query criteria. The syntax is var (expression).　　An equation can be a field name, an expression, or a function that can be an internal or consumer defined, but not a SQL function. If the record that meets the query criteria is two or less, the VAR function returns a null value, which indicates that the variance number cannot be computed. Select min (c) from (select min (b) as C from Tab_nameunionselect min (a) as C from Tab_name)

How to use the average, total, minimum, maximum

of SQL functions