Description

The SQL Aggregate Functions can be used to perform calculations on a set of values and return a single value, based on the type of functions.

  • These can be used only with a SELECT statement.
  • These perform calculations on a set of values and return a single value.

Let's look at their syntax and examples to understand how to use them.

Aggregate Functions

Here is a list of aggregate functions.

Function Description
COUNT() Returns the number of rows in a result set.
SUM() Returns the sum of a set of values.
AVG() Returns the average of a set of values.
MAX() Returns the maximum value of a set of values.
MIN() Returns the minimum value of a set of values.

Syntax

Here is the basic syntax of the SELECT statement using aggregate functions.

  • The column_name represents the column name on which the function must be applied.
  • The table_name is the name of the table.
SELECT COUNT(*) FROM employees;

The below SELECT statements execute on a specific column, which performs calculations on the values from a specific column.

SELECT COUNT(column_name) FROM table_name;
SELECT SUM(column_name) FROM table_name;
SELECT AVG(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;
SELECT MIN(column_name) FROM table_name;

Table Date

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper NULL 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

SELECT With COUNT() Function

The below SELECT statement returns the number of rows in a result set.

Run this on IDE

SELECT COUNT(*) FROM employees;

After successful execution, the output contains the below data.

COUNT(*)
5

The below SELECT statement returns the number of rows in a result set, considering only the non-NULL values on the specified column.

Run this on IDE

SELECT COUNT(gender) FROM employees;

After successful execution, the output contains the below data.

COUNT(gender)
4

SELECT With SUM() Function

The below SELECT statement returns the sum of a set of values.

Run this on IDE

SELECT SUM(salary) FROM employees;

After successful execution, the output contains the below data.

SUM(salary)
252000

SELECT With AVG() Function

The below SELECT statement returns the average of a list of values.

Run this on IDE

SELECT AVG(salary) FROM employees;

After successful execution, the output contains the below data.

AVG(salary)
50400

SELECT With MAX() Function

The below SELECT statement returns the maximum value of a set of values.

Run this on IDE

SELECT MAX(salary) FROM employees;

After successful execution, the output contains the below data.

MAX(salary)
89000

SELECT With MIN() Function

The below SELECT statement returns the minimum value of a set of values.

Run this on IDE

SELECT MIN(salary) FROM employees;

After successful execution, the output contains the below data.

MIN(salary)
25000

SELECT With All Aggregate Functions

The below SELECT statement returns the values for all aggregate functions.

Run this on IDE

SELECT COUNT(*), COUNT(gender), SUM(salary), AVG(salary), MAX(salary), MIN(salary) FROM employees;

After successful execution, the output contains the below data.

COUNT(*) COUNT(gender) SUM(salary) AVG(salary) MAX(salary) MIN(salary)
5 4 252000 50400 89000 25000

Overall

We now understood how to use aggregate functions.

Related Links