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

Bhanu Priya

Updated on 03-Jul-2021 09:02:50

• Related Questions & Answers
• Explain the concept of DYNAMIC SQL in DB2 with the help of an example
• Write queries using aggregate functions in relational algebra (DBMS)?
• What are COLUMN functions in DB2? Explain with the help of an example
• Explain different comparison operators used in SQL queries
• Explain SQL describing COUNT aggregate and CURRENT DATE function
• Aggregate Functions in DBMS
• Explain the concept of LOCK PROMOTION with the help of an example
• Explain join operations with the help of an example in DBMS
• Explain the concept of magnetic disk with the help of a diagram(DBMS)
• How to identify SQL queries with the most waits in Oracle?
• Explain the functions of Session Layer.
• Explain the functions of Presentation Layer.
• What is the purpose of COALESCE function? Explain with the help of an example.
• Explain SHARED, UPDATE and EXCLUSIVE locks with the help of an example
• String Functions in SQL