# 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