SQL Aggregate Functions

Summary: in this tutorial, you will learn about the SQL aggregate functions including AVG(), COUNT(), MIN(), MAX(), and SUM().

An SQL aggregate function calculates on a set of values and returns a single value. For example, the average function ( AVG) takes a list of values and returns the average.

Because an aggregate function operates on a set of values, it is often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause divides the result set into groups of values and the aggregate function returns a single value for each group.

The following illustrates how the aggregate function is used with the GROUP BY clause:

SELECT c1, aggregate_function(c2)
FROM table
GROUP BY c1;Code language: SQL (Structured Query Language) (sql)

The following are the commonly used SQL aggregate functions:

•  AVG() – returns the average of a set.
•  COUNT() – returns the number of items in a set.
•  MAX() – returns the maximum value in a set.
•  MIN() – returns the minimum value in a set
•  SUM() – returns the sum of all or distinct values in a set

Except for the COUNT() function, SQL aggregate functions ignore null.

You can use aggregate functions as expressions only in the following:

• The select list of a SELECT statement, either a subquery or an outer query.
• A HAVING clause

 AVG

The AVG() function returns the average values in a set. The following illustrates the syntax of the AVG() function:

AVG( ALL | DISTINCT)Code language: SQL (Structured Query Language) (sql)

The ALL keyword instructs the AVG() function to calculate the average of all values while the DISTINCT keyword forces the function to operate on distinct values only. By default, the ALL option is used.

The following example shows how to use the AVG() function to calculate the average salary of each department:

SELECT 
    department_name, ROUND(AVG(salary), 0) avg_salary
FROM
    employees
        INNER JOIN
    departments USING (department_id)
GROUP BY department_name
ORDER BY department_name;Code language: SQL (Structured Query Language) (sql)

 MIN

The MIN() function returns the minimum value of a set. The following illustrates the syntax of the MIN() function:

MIN(column | expression)Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns the minimum salary of the employees in each department:

SELECT 
    department_name, MIN(salary) min_salary
FROM
    employees
        INNER JOIN
    departments USING (department_id)
GROUP BY department_name
ORDER BY department_name;Code language: SQL (Structured Query Language) (sql)

 MAX

The MAX() function returns the maximum value of a set. The MAX() function has the following syntax:

MAX(column | expression)Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns the highest salary of employees in each department:

SELECT 
    department_name, MAX(salary) highest_salary
FROM
    employees
        INNER JOIN
    departments USING (department_id)
GROUP BY department_name
ORDER BY department_name;Code language: SQL (Structured Query Language) (sql)

 COUNT

The COUNT() function returns the number of items in a set. The following shows the syntax of the COUNT() function:

COUNT ( [ALL | DISTINCT] column | expression | *)Code language: SQL (Structured Query Language) (sql)

For example, the following example uses the COUNT(*) function to return the headcount of each department:

SELECT 
    department_name, COUNT(*) headcount
FROM
    employees
        INNER JOIN
    departments USING (department_id)
GROUP BY department_name
ORDER BY department_name;Code language: SQL (Structured Query Language) (sql)

 SUM

The SUM() function returns the sum of all values. The following illustrates the syntax of the SUM() function:

SUM(ALL | DISTINCT column)Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns the total salary of all employees in each department:

SELECT 
    department_id, SUM(salary)
FROM
    employees
GROUP BY department_id;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned the most commonly used SQL aggregate functions including AVG(), COUNT(), MIN(), MAX(), and SUM() functions.