Explain aggregate functions with the help of SQL queries

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.

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