SQL Functions
- Previous Page SQL Server
- Next Page SQL avg()
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 |
- Previous Page SQL Server
- Next Page SQL avg()