SQL Functions With Examples

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

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

More From Max ReynoldsDifferences Between SOQL and SQL Explained

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.

Read More About SQLHow to Use SQL in Python

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.