SQL Functions

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.