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 |
No comments:
Post a Comment