SQL Functions
SQL has a lot of built-in functions for counting and calculations.
Function Syntax
The syntax for built-in SQL functions is:
SELECT function(column) FROM table |
Types of Functions
There are several basic types and categories of functions in SQL. The basic types of functions are:
- Aggregate Functions
- Scalar functions
Aggregate functions
Aggregate functions operate against a collection of values, but return a
single value.
Note: If used among many other expressions in the item list of a
SELECT statement, the SELECT must have a GROUP BY clause!!
"Persons" table (used in most examples)
| Name |
Age |
| Hansen, Ola |
34 |
| Svendson, Tove |
45 |
| Pettersen, Kari |
19 |
Aggregate functions in MS Access
| Function |
Description |
| AVG(column) |
Returns the average value of a column |
| COUNT(column) |
Returns the number of rows (without a NULL
value) of a column |
| COUNT(*) |
Returns the number of selected rows |
| FIRST(column) |
Returns the value of the first record in a
specified field |
| LAST(column) |
Returns the value of the last record in a
specified field |
| MAX(column) |
Returns the highest value of a column |
| MIN(column) |
Returns the lowest value of a column |
| STDEV(column) |
|
| STDEVP(column) |
|
| SUM(column) |
Returns the total sum 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 (without a NULL
value) of a column |
| COUNT(*) |
Returns the number of selected rows |
| COUNT(DISTINCT column) |
Returns the number of distinct results |
| FIRST(column) |
Returns the value of the first record in a
specified field (not supported in SQLServer2K) |
| LAST(column) |
Returns the value of the last record in a
specified field (not supported in SQLServer2K) |
| MAX(column) |
Returns the highest value of a column |
| MIN(column) |
Returns the lowest value of a column |
| STDEV(column) |
|
| STDEVP(column) |
|
| SUM(column) |
Returns the total sum of a column |
| VAR(column) |
|
| VARP(column) |
|
Scalar functions
Scalar functions operate against a single value, and return a single value
based on the input value.
Useful Scalar Functions in MS Access
| Function |
Description |
| UCASE(c) |
Converts a field to upper case |
| LCASE(c) |
Converts a field to lower case |
| MID(c,start[,end]) |
Extract characters from a text field |
| LEN(c) |
Returns the length of a text field |
| INSTR(c,char) |
Returns the numeric position of a named
character within a text field |
| LEFT(c,number_of_char) |
Return the left part of a text field
requested |
| RIGHT(c,number_of_char) |
Return the right part of a text field
requested |
| ROUND(c,decimals) |
Rounds a numeric field to the number of
decimals specified |
| MOD(x,y) |
Returns the remainder of a division
operation |
| NOW() |
Returns the current system date |
| FORMAT(c,format) |
Changes the way a field is displayed |
| DATEDIFF(d,date1,date2) |
Used to perform date calculations |
|
HOME
Printer friendly page
