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 |
Learn XML with <oXygen/> XML Editor - Free Trial!
 |
|
oXygen helps you learn to define,
edit, validate and transform XML documents. Supported technologies include XML Schema,
DTD, Relax NG, XSLT, XPath, XQuery, CSS.
Understand in no time how XSLT and XQuery work by using the intuitive oXygen debugger!
Do you have any XML related questions? Get free answers from the oXygen
XML forum
and from the video
demonstrations.
Download a FREE 30-day trial today!
|
|
HOME
Printer friendly page
