
- DBMS Tutorial
- DBMS - Home
- DBMS - Overview
- DBMS - Architecture
- DBMS - Data Models
- DBMS - Data Schemas
- DBMS - Data Independence
- Entity Relationship Model
- DBMS - ER Model Basic Concepts
- DBMS - ER Diagram Representation
- DBMS - Generalization, Aggregation
- Relational Model
- DBMS - Codd's Rules
- DBMS - Relational Data Model
- DBMS - Relational Algebra
- DBMS - ER to Relational Model
- DBMS- SQL Overview
- Relational Database Design
- DBMS - Database Normalization
- DBMS - Database Joins
- Storage and File Structure
- DBMS - Storage System
- DBMS - File Structure
- Indexing and Hashing
- DBMS - Indexing
- DBMS - Hashing
- Transaction And Concurrency
- DBMS - Transaction
- DBMS - Concurrency Control
- DBMS - Deadlock
- Backup and Recovery
- DBMS - Data Backup
- DBMS - Data Recovery
- DBMS Useful Resources
- DBMS - Quick Guide
- DBMS - Useful Resources
- DBMS - Discussion
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 Articles
- Write queries using aggregate functions in relational algebra (DBMS)?
- Explain the concept of DYNAMIC SQL in DB2 with the help of an example
- Explain different comparison operators used in SQL queries
- Explain SQL describing COUNT aggregate and CURRENT DATE function
- What are COLUMN functions in DB2? Explain with the help of an example
- How to customize the Result of JPA Queries with Aggregation Functions?
- Aggregate Functions in DBMS
- How to identify SQL queries with the most waits in Oracle?
- SQL query describing usage of SUM aggregate function and GROUP-BY with HAVING
- Different Types of SQL Functions
- With the help of diagram, explain digestive system.
- What is the SQL query describing usage of MAX aggregate function and GROUP-BY with HAVING?
- String Functions in SQL
- Mathematical Functions in SQL
- Conversion Functions in SQL
