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.

Functions 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
Adam Daniels 1989-01-13
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
ADAM daniels
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)
Doe c947ad320e66fc64998e86a55c0da210c8c1d81a
Daniels 821e1c7e4537a53bc42c34f2da8af6a6564294ab
Roosevelt b8a1d4bcd11619a82223864ab31830715e6ac045
French 44389f6a466eaabf5c1db729e369511134e2b03b
Provide feedback on this page
Comments are sent via email to me.