# Explain aggregate functions with the help of SQL queries

DBMSDatabaseBig Data Analytics

Aggregate functions perform Calculation on a set of values and return a single value. They ignore NULL values except COUNT and are used with GROUP BY clause of SELECT statement.

## Types of aggregate functions

The different types of aggregate functions are −

• AVG
• MAX
• MIN
• SUM
• COUNT()
• COUNT(*)

Let’s consider an employee table. We will perform the calculations on this table by using aggregate functions.

EidEnameAgeCitySalary
E001ABC29Pune20000
E002PQR30Pune30000
E003LMN25Mumbai5000
E004XYZ24Mumbai4000
E005STU32Bangalore25000

## Average function

The keyword used to calculate the average of given items is AVG. It returns the average of the data values.

Syntax

The syntax is as follows −

select <column name> from <table name>;

Example

An example of use of average function is as follows −

select AVG(salary) from employee;

Output

You will get the following output −

AVG(salary)= 16800

## Maximum function

The keyword used to return maximum value for a column is MAX.

Syntax

The syntax is as follows −

select MAX <column name> from <table name>;

Example

An example of use of maximum function is as follows −

select MAX(salary) from employee;

Output

You will get the following output −

Max(salary)=30000

## Minimum function

The keyword used to return the minimum value for a column is MIN.

Syntax

The syntax is as follows −

select MIN <column-name> from <table-name>;

Example

An example of use of minimum function is as follows −

select MIN (salary) from employee;

Output

You will get the following output −

MIN(salary)=4000

## SUM function

It returns the sum(addition) of the data values. The keyword used to perform addition on data items is SUM.

Syntax

The syntax is as follows −

select SUM <column-name> from <table-name>;

Example

An example of use of SUM function is as follows −

select SUM (salary) from employee where city=’Pune’;

Output

You will get the following output −

SUM (salary)= 50000

## COUNT function

It returns the total number of values in a given column.

Syntax

The syntax is given below −

select COUNT <column-name> from <table-name>;

Example

An example of use of COUNT function is as follows −

select COUNT(Empid) from employee;

Output

You will get the following output −

COUNT(Empid)= 5

## COUNT(*) function

It returns the number of rows in a table.

Syntax

The syntax is as follows −

select COUNT(*) from <table-name>;

Example

An example of use of COUNT(*) function is as follows:

select COUNT(*) from employee;

Output

You will get the following output −

COUNT(*) =5
Updated on 03-Jul-2021 09:02:50