To use this website fully, you first need to accept the use of cookies. By agreeing to the use of cookies you consent to the use of functional cookies. For more information read this page.

# Part 5.1Functions in SQL

SQL provides several functions.

This part of this tutorial will focus on several SQL functions. The following table will be used:

Purchases

product_id price
001 $270.00 001$252.00
003 $120.00 002$190.00

## Aggregate functions

An aggregate function is a function that groups the values of multiple rows and uses this as an input to a function.

### Count

The SQL count function is designed to count the number of rows. With the example table, there would be four rows. This can be returned in the results table by using:

SQL
SELECT COUNT(*) FROM Purchases


The results table would look like:

COUNT(*)
4

As with all aggregate functions, it is also possible to count a specific field.

SQL
SELECT COUNT(price) FROM Purchases


Note that null values will not be counted, so in the previous example where a null value was returned, there would still be four rows, since one value was null.

### Sum

The sum aggregate function is designed to calculate the total of a field, or set of fields. With the above sample table the following code would produce the results as follows:

SQL
SELECT SUM(price) FROM Purchases

SUM(price)
4

Note that the sum function must use one field, such as, and in this case, price.

### Average

SQL also provides an average function, designed to calculate the average of a field. It works the same way as the sum function.

The average of a set of numbers is the total or sum of the numbers divided by the count of numbers.

SQL
SELECT AVG(price) FROM Purchases

AVG(price)
208.00

### Minimum and maximum

The final two aggregate functions provided in MySQL are the minimum and maximum functions. In the following example they have been combined into one single statement that will return a single results table with both the minimum and maximum values in one:

SQL
SELECT MIN(price), MAX(price) FROM Purchases

MIN(price) MAX(price)
120 270

## Scalar functions

Scalar functions are designed to work on each individual row and only return a single value based on the input. This tutorial only gives a few since there are too many to name and since they vary by distribution.

### Upper case and lower case

SQL can also transform strings or text into upper and lower case variants. For the following example the samples used will be the Attendees table:

forename surname dob
Jane Doe 1991-12-01
Frank Roosevelt 1992-04-19
Jane French 1995-02-28
SQL
SELECT UCASE(forename), LCASE(surname) FROM Attendees


The result of this query would be:

UCASE(forename) LCASE(surname)
JANE doe
FRANK roosevelt
JANE french

### SHA1

Another function that can be fairly useful is the SHA1 aggregate function. This will return the result of a Secure-Hash Algorithm 1 function applied to a string.

SQL
SELECT surname, SHA1(surname) FROM Attendees


The results table would look like:

surname SHA1(surname)