GroTechMinds

GroTechMinds logo
aggregate function in sql

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
SQL 2
Department Table
sql 2

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:
sQL 3

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:
Sql 4

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:
Sql 5

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:
sql 6

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:
sql 7
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:
Sql 8

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:
sql 9
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
Share:
Upskill Yourself
Consult Us