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;
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
the statement, either a subquery or an outer query. - A
HAVING
clause
AVG
The AVG()
the 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
the 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()
the 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()
the function returns the maximum value of a set. The MAX()
the 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()
the 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()
the function returns the sum of all values. The following illustrates the syntax of the SUM()
function:
SUM(ALL | DISTINCT column)
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;
In this tutorial, you have learned the most commonly used SQL aggregate functions including AVG()
, COUNT()
, MIN()
, MAX()
, and SUM()
functions.
0 Comments
CAN FEEDBACK
Emoji