SQL functions are pre-written actions that can be called on individual cells, multiple cells in a record or multiple records.

## 3 Types of SQL Functions, Defined

**SQL aggregate functions** are used to summarize a set of values and return a single value.

**SQL scalar functions** are user-defined or built-in functions that take one or more parameters and return a single value.

**SQL character functions** are a type of scalar function used to manipulate and transform character data, such as strings.

There are two main types of SQL functions: aggregate and scalar functions.

## Aggregate Functions

Aggregate functions are used to summarize a set of values and return a single value. Common aggregate functions include `COUNT`

, `SUM`

, `AVG`

, `MIN`

and `MAX`

. These functions can be used to quickly calculate statistics such as the number of rows in a table, the total value of a specific column, the average value of a column, the minimum or maximum value in a column, or any of these statistics on a conditional subset of records.

For example, consider a `“Weather”`

table with the following columns:

- “City” (varchar)
- “State” (varchar)
- “Weather_Date” (date)
- “High_Temperature” (int)
- “Low_Temperature” (int)
- “Precipitation” (float)
- “Description” (varchar)

### AVG

Using the `“Weather”`

table, we could use the `AVG`

function to calculate the average high temperature for all days in the table:

or the average high temperature for all days in Phoenix.

### COUNT

We can use the `COUNT`

function to calculate the number of records in the table:

or the number of rainy days in Phoenix.

### MIN/MAX

We can use the `MIN`

aggregate function to find the lowest temperature recorded:

and the `MAX`

function to find the highest temperature in the table:

We can also use the `GROUP BY`

clause to group the records in the table by city and then use aggregate functions to find the sum, average, minimum, and maximum high temperature for each city:

Scalar Functions

Scalar functions are user-defined or built-in functions that take one or more parameters and return a single value. Scalar functions can be used in `SELECT`

, `WHERE`

and `HAVING`

clauses to transform data or perform calculations on it.

### User-defined Scalar Functions

We can create a scalar function to convert the temperature from Fahrenheit to Celsius using the following SQL code:

Then, we can use this scalar function in a `SELECT`

statement to display the temperature in Celsius:

Another example: We could create a scalar function to check if it rained on a certain day using the following SQL:

Then, we can use this scalar function in a `SELECT`

statement to display whether it rained on a certain day:

Note that scalar functions can also take multiple parameters and can generally be used in `WHERE`

, `GROUP BY`

and `HAVING`

clauses as well. Additionally, the syntax of user-defined functions differs slightly across different database management systems (e.g. MySQL, SQL Server, PostgreSQL, etc.).

## Character Functions

In SQL, character functions are a type of scalar function used to manipulate and transform character data, such as strings. Some examples of character functions in SQL include:

### CONCAT

This `CONCAT`

function concatenates two or more strings together. For example, we can use `CONCAT`

to combine the city and state columns in our `"Weather"`

table:

### LENGTH

The length function returns the number of characters in a string. For example, we can use `LENGTH`

to find the number of characters in the city column:

### UPPER/LOWER

The `UPPER`

and `LOWER`

functions converts all characters in a string to uppercase. For example, we could use `LOWER`

to display the city names in uppercase:

If our city column has inconsistent capitalization, we might use `UPPER`

in a `GROUP BY`

clause.

### SUBSTRING

The `SUBSTRING`

function returns a portion of a string, specified by a starting position and length. For example, we could use `SUBSTRING`

to return the first three characters of the city name:

The parameters for `SUBSTRING`

method are the string, the start character (`1`

) and the length of the substring (`3`

).

### REPLACE

The `REPLACE`

function replaces all occurrences of a specified string with another string. For example, we could use `REPLACE`

to replace the description `“Sunny”`

with `“Sun”`

:

`REPLACE`

can also be nested if we have a more complicated procedure:

This replaces all `“Sunny”`

substrings with `“Sun”`

and `“Rainy”`

substrings with `“Rain”`

.

## Numeric Scalar Functions

Finally, numeric scalar functions are functions that operate on a single value and return a single value of a numeric data type. One example of a scalar numeric function is:

### ROUND

`ROUND`

takes a numeric data and an integer for the number of decimal places and returns the rounded data. For example, we can return the high temperature in all records rounded to the nearest 1 decimal place:

Other numeric scalar functions include `ABS`

for taking the absolute value of a number, `CEILING`

, `FLOORbuit`

and `SQRT`

.

## SQL Functions Summarized

SQL aggregate functions and scalar functions are important tools that can be used to flexibly manipulate and extract information from a database. Other groups of built-in functions that act on specific data types such as date functions like `MONTH()`

can be used similarly to other user-defined and built-in scalar functions. Finally, more advanced functions such as type-casting functions or user-defined table-valued functions (which return a table rather than a scalar value) are worth exploring once you are comfortable with the basics of SQL functions.

Understanding how to use functions can greatly enhance the functionality of SQL queries and help to make data analysis more efficient and accurate. SQL functions are a fundamental aspect of SQL and are a must-have tool for anyone working with SQL databases.