SQL Aggregate functions

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;

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)

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;

MIN

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

MIN(column | expression)

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;

 MAX

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

MAX(column | expression)

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;

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 | *)

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;

SUM

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

SUM(ALL | DISTINCT column)