AGGREGATE FUNCTION IN SQL
After reading this blog you will be able to answer the questions mentioned below under 'Take Quiz' section
Introduction
The aggregate function in SQL performs a calculation on multiple values and returns a single value. Aggregate functions should not be used in the ‘where’ clause we can use them in the ‘have’ clause. Aggregate function is used in the ‘select’ clause.
There are different types of aggregate functions in SQL.
Employee Table
Department Table
1 .Max– It is used to find the maximum number like the maximum salary.
Syntax:
select max(CN)
from TN;
Problem Statement:
Display the maximum salary from the employee table.
Query:
select max(salary) as highest_salary
from employee;
Output Screen:
2. Min– It is used to find the minimum number like the minimum salary.
Syntax:
select min(CN)
from TN;
Problem Statement:
Display the minimum salary from the employee table.
Query:
select min(salary) as minimum_salary
from employee;
Output Screen:
3. Sum– It is used to find the sum of values like the total salary.
Syntax
select min(CN)
from TN;
Problem Statement:
Display the total salary of dept_no 10.
Query:
select sum(salary) as total_salary
from employee
Where dept_no=10;
Output Screen:
4. Avg– It is used to find the average of values like the average salary.
Syntax
select avg(CN)
from TN;
Problem Statement:
Display the average salary of all the employees from dep_no=10 and dept_no=30.
Query:
select avg(salary) as avg_salary
from employee
where dept_no in (10,30);
Output Screen:
5. Count– It is used to find the count of values like the count number of rows in any table.
Syntax
select count(CN)
from TN;
Problem Statement:
Display the number of rows present in any table.
Query:
select count(*)
from employee;
Output Screen:
Mixed Problems on aggregate function for a better understanding of the topic
Problem Statement:
Display the average, minimum, and maximum salary of each dept_no.
Query:
select max(salary) as highest_salary,min(salary) as minimum_salary,avg(salary) as average_salary
from employee;
Output Screen:
Problem Statement:
Display the maximum salary excluding those employees whose names start with ‘s’.
Query:
select max(salary) as highest_salary
from employee
Where ename not like ‘s%’;
Output Screen:
Conclusion
The aggregate function in SQL operates on a group of values and returns a single result by max(), min(), sum(), count(), and avg(). With the help of these functions, we can find the maximum, minimum, total, average, and count of values to get results in single values.
Also Read: Sorting and Grouping in SQL
Test Your Knowledge
Popular Courses
Previous
Next
Upskill Yourself
Consult Us
Consult Us