SQL Functions

SQL has many built-in functions that can be used for counting and calculation.

Syntax of function

The syntax of built-in SQL functions is:

SELECT function(column) FROM Table

Type of function

In SQL, there are several basic types and categories of functions. The basic type of function is:

  • Aggregate functions
  • Scalar functions

Aggregate functions (Aggregate functions)

The operation of Aggregate functions is aimed at a series of values and returns a single value.

Note:If the SELECT statement is used in many other expressions in the item list of the SELECT statement, then this SELECT must use the GROUP BY statement!

"Persons" table (used in most examples)

Name Age
Adams, John 38
Bush, George 33
Carter, Thomas 28

Aggregate functions in MS Access

Function Description
AVG(column) Returns the average value of a column
COUNT(column) Returns the number of rows in a column (excluding NULL values)
COUNT(*) Returns the number of selected rows
FIRST(column) Returns the value of the first record in the specified field
LAST(column) Returns the value of the last record in the specified field
MAX(column) Return the maximum value of a column
MIN(column) Return the minimum value of a column
STDEV(column) 　
STDEVP(column) 　
SUM(column) Return the total of a column
VAR(column) 　
VARP(column) 　

Aggregate functions in SQL Server

Function Description
AVG(column) Returns the average value of a column
BINARY_CHECKSUM 　
CHECKSUM 　
CHECKSUM_AGG 　
COUNT(column) Returns the number of rows in a column (excluding NULL values)
COUNT(*) Returns the number of selected rows
COUNT(DISTINCT column) Return the number of distinct results
FIRST(column) Return the value of the first record in the specified field (not supported by SQLServer2000)
LAST(column) Return the value of the last record in the specified field (not supported by SQLServer2000)
MAX(column) Return the maximum value of a column
MIN(column) Return the minimum value of a column
STDEV(column) 　
STDEVP(column) 　
SUM(column) Return the total of a column
VAR(column) 　
VARP(column) 　

Scalar functions

Scalar functions operate on a single value and return a single value based on the input value.

Scalar functions in MS Access

Function Description
UCASE(c) Convert a field to uppercase
LCASE(c) Convert a field to lowercase
MID(c,start[,end]) Extract characters from a text field
LEN(c) Return the length of a text field
INSTR(c,char) Return the numeric position of a specified character in a text field
LEFT(c,number_of_char) Return the left part of a requested text field
RIGHT(c,number_of_char) Return the right part of a requested text field
ROUND(c,decimals) Round a numeric field to a specified number of decimal places
MOD(x,y) Return the remainder of the division operation
NOW() Return the current system date
FORMAT(c,format) Change the display style of a field
DATEDIFF(d,date1,date2) Used for date calculations