Statistics, arithmetic, and string functions of common SQL Server Functions

Statistical functions

ØAVG (expression) function ---- calculate the average value by Column

ØSum (expression) function ---- calculate the sum of values by Column

Example: average score and total score of students whose student ID is S1

Select AVG (score) as average score, sum (score) as total score from
SC where (SNO = 's1 ')

ØMax (expression) function ---- calculate the maximum value in a column

ØMin (expression) function ---- calculate the minimum value in a column

For example, calculate the highest score, lowest score, and the difference between the two of the C1 courses.

Select max (score) as highest score, min (score) as lowest score, max (score)-min (score)
As maximum score
Difference from SC where (CNO = 'c1 ')

ØCount (expression) function ---- count by column Value

For example, calculate the total number of students in the computer department.

Select count (SNO) from s where dept = 'put'

Note: After the keyword distinct is added, duplicate rows are eliminated. The number of different values of 'dept' can be calculated. The count function does not calculate the null value, but calculates the value of 0.

For example, count the number of students with scores

Select count (score) from SC

ØThe count (*) function is used to count the number of tuples. Duplicate rows are not eliminated. The distinct keyword is not allowed.

For example, calculate the total number of students in the computer department.

Selectcount (*) from
S where dept = 'put'

ØSTDev (expression) function ---- returns the standard deviation of all data in the expression. The expression is generally a numeric column in a table, or a column similar to the numeric type, such as the money type, except for the bit type.
The Null Value in the expression is ignored. The return value is of the float type.

ØStdevp function ---- returns the population standard deviation of all data in the expression (expression and return value type are the same as STDev function)

 

ØVaR function ---- returns the statistical variation of all data in the expression. (Expression and return value types are the same as STDev functions)

ØVarp function ---- returns the total variation of all data in the expression. Expression and return value are of the same type as the STDev function)

 

Arithmetic functions

String Functions

Character conversion functions

 

Space-removing Function

  

String Function
String comparison functions

 

String operation functions