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 |
 |
|
Get Your Diploma!
W3Schools' Online Certification Program is the perfect solution for busy
professionals who need to balance work, family, and career building.
The HTML Certificate is for developers who want to document their knowledge of HTML, XHTML, and CSS.
The ASP Certificate is for developers who want to document their knowledge of ASP, SQL, and ADO.
|
|
HOME
Printer friendly page
