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

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:

SELECT COUNT(*) FROM `Purchases`
		

The results table would look like:

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

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:

SELECT SUM(price) FROM `Purchases`
		

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.

SELECT AVG(price) FROM `Purchases`
		

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:

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

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:

SELECT UCASE(forename), LCASE(surname) FROM `Attendees`
		

The result of this query would be:

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.

SELECT surname, SHA1(surname) FROM `Attendees`
		

The results table would look like: