

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
Eid | Ename | Age | City | Salary |
---|---|---|---|---|
E001 | ABC | 29 | Pune | 20000 |
E002 | PQR | 30 | Pune | 30000 |
E003 | LMN | 25 | Mumbai | 5000 |
E004 | XYZ | 24 | Mumbai | 4000 |
E005 | STU | 32 | Bangalore | 25000 |
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
- 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