Aggregate functions
Aggregate functions in DBMS
- SQL provides a number of built-in functions to perform operations on data these functions are very much useful for performing mathematical calculations on table data
Aggregate functions
return a single value after performing calculations on a set of values, here will discuss the five frequently used aggregate functions provided by SQL- These aggregate functions are used with the SELECT statement at a time only one column can be applied with the function
General syntax
SELECT functionname(column_name) FROM table_name

consider sample table emp
avg()
This function returns the Arithmetic mean of all the values present in that column
eid | name | age | salary |
65 | Trish | 22 | 9000 |
66 | Rishi | 29 | 8000 |
67 | Mahi | 34 | 6000 |
68 | Mani | 44 | 10000 |
69 | Puppy | 35 | 8000 |
SQL query to find average salary
SELECT avg(salary) from Emp;
avg(salary) |
8200 |
count()
It returns the number of rows present in the table which can be either based upon a condition or without a condition
SQL query to count employees, satisfying specified condition is,
SELECT COUNT(name) FROM Emp WHERE salary = 8000;
O/P
count(name) |
2 |
Example of COUNT(distinct)
Consider the following Emp table
eid | name | age | salary |
65 | Trish | 22 | 9000 |
66 | Rishi | 29 | 8000 |
67 | Mahi | 34 | 6000 |
68 | Mani | 44 | 10000 |
69 | Puppy | 35 | 8000 |
SQL query is,
SELECT COUNT(DISTINCT salary) FROM emp;
O/P
count(distinct salary) |
4 |
max()
It returns the maximum i.e the largest value among all the values present in that column
SQL query to find the Maximum salary
SELECT MAX(salary) FROM emp;
O/P
MAX(salary) |
10000 |
min()
It returns the minimum value i.e the smallest numerical value among all the values present in that particular column
SQL query to find minimum salary
SELECT MIN(salary) FROM emp;
Result will be,
MIN(salary) |
6000 |
sum()
The sum function returns the arithmetic sum of all the values present in that column
SQL query to find sum of salaries
SELECT SUM(salary) FROM emp;
Result of above query is,
SUM(salary) |
41000 |
Find the 2nd highest salary of an employee(interview question)
- This is the most commonly asked interview question. Hence I recommend you remember this example
- This solution uses a sub query to first exclude the maximum salary from the data set and then again finds the maximum salary, which is effectively the second maximum salary from the Employee table.
select max(salary) from employee where salary < ( select max(salary) from emp);
0/P
MAX(salary) |
9000 |
How it works
- Here first we are creating a result set excluding first highest salary i.e max(salary )
- Again applying max(sal) on this result would Fetch the second highest salary obviously
Note
Whenever we are using aggregate functions in a select statement you are not allowed to use other columns
SELECT avg(salary),eid from Emp;//error
In a select statement you can use only one aggregate function at a time