Aggregate functions Objective understand able to use aggregate

Aggregate functions Objective- understand able to use aggregate functions in select statement • Aggregate functions are used to implement calculation based upon a particular column. • These functions always return a single value.

Aggregate functions function purpose Sum() To find the sum of values of an attribute Avg() To find the average of values of an attribute Max() To find the highest values of an attribute Min() To find the lowest value of an attribute Count(attribute) It gives the no. of non-null values in the particular attribute Count(*) It gives the no. of tuples (including null )

Aggregate function Ex: 1) Select max(salary) from employee; 2) Select min(salary) from employee; Output -1 TABLE - EMPLOYEE ID NAME SALARY place max(salary) 1 ARUN 6000 Dubai 12000 2 VARUN 8000 Sharjah 3 ALI 9000 Ajman Output -2 4 GEORGE 12000 Ajman min(salary) 5 MOHD 8000 dubai 6000

Aggregate function Ex: Select avg(salary) from employee; Select sum(salary) from employee; Select count (*) from employee; TABLE - EMPLOYEE ID NAME SALARY place 1 ARUN 6000 Dubai 2 VARUN 8000 Sharjah 3 ALI 9000 Ajman count(*) 4 GEORGE 12000 Ajman 5 5 MOHD 8000 dubai Output -3

Aggregate function Ex: Select max(salary) from employee where place = ‘Dubai’; TABLE - EMPLOYEE ID NAME SALARY place 1 ARUN 6000 Dubai 2 VARUN 8000 Sharjah 3 ALI 9000 Ajman 4 GEORGE 12000 Ajman 5 MOHD 8000 Dubai Output -1 max(salary) 8000

Aggregate function Ex: Select COUNT(*) from employee where place = ‘Dubai’; TABLE - EMPLOYEE ID NAME SALARY place 1 ARUN 6000 Dubai 2 VARUN 8000 Sharjah 3 ALI 9000 Ajman 4 GEORGE 12000 Ajman 5 MOHD 8000 Dubai Output -1 COUNT(*) 2

Aggregate function Ex: 1) Select COUNT(*) from employee; 2) Select COUNT(MARK) from employee; TABLE - EMPLOYEE Output -1 ID NAME MARK COUNT(*) 1 ARUN 50 5 2 VARUN 3 ALI 80 4 GEORGE 90 COUNT(mark) 5 MOHD 78 4 Output -2