SQL has many built-in functions that can be used for counting and calculation.
SQL Aggregate function
The SQL Aggregate function calculates the value obtained from a column and returns a single value.
Useful Aggregate functions:
- AVG ()-return average
- COUNT ()-Returns the number of rows
- FIRST ()-returns the value of the first record
- LAST ()-returns the value of the last record
- MAX ()-returns the maximum value
- MIN ()-return the minimum
- SUM ()-return the total
SQL Scalar function
The SQL Scalar function returns a single value based on the input value.
Useful Scalar functions:
- UCASE ()-convert a field to uppercase
- LCASE ()-convert a field to lowercase
- MID ()-extract characters from a text field, used in MySql
- SubString (field, 1, end)-extract characters from a text field
- LEN ()-Returns the length of a text field
- ROUND ()-round a specified number of decimal places to a numeric field
- NOW ()-returns the current system date and time
- FORMAT ()-Format how a field is displayed
Tip: In the following sections, we will explain the Aggregate function and Scalar function in detail.