Aggregate Function
Aggregate Function: The aggregate function is also required in many database applications for generating reports. SQL provides this feature also. SQL provides built-in aggregate functions that are mostly used to generate summary reports. The aggregate functions operate rows of table group-wise and give one result per group.
Functions are very powerful feature of SQL. A function may accept argument as input and returns a value as output. An argument may be a constraint value, variable name, column name or expression.
Aggregate functions have following function.
- COUNT
- SUM
- AVG
- MAX
- MIN
The EMPOYEE table have the following data which will be used for following aggregate functions
| Emp_ID | Name | Department | Salary |
| ADM-11 | Ali | Admin | 25000 |
| MKT-22 | Ahmad | Marketing | 26000 |
| ADM-33 | Gohar | Admin | 30000 |
| MKT-45 | Usman | Marketing | 30000 |
| MKT-56 | Usama | Marketing | 30000 |
COUNT Function
The COUNT function returns the number of rows or value, selected by the query from the table. For example to count the total number of EMPLOYEE table the SELECT statement using COUNT function is given below:
SELECT COUNT (*) FROM EMPLOYEE
The output of the above statement will be 5
SUM Function
The SUM function is used to compute the sum of values of specific column of numeric data type. For example, to compute the sum of values of Salary column of EMPLOYEE table, the SELECT statement using SUM function is:
SELECT SUM (SALARY) FROM EMPOLYEE
The output if the following statement will be 141000
Similarly to compute the salary of all those employees that are in ‘Admin’ department, the statement is:
SELECT SUM (SALARY) FROM EMPLOYEE WHERE Department = ‘Admin’
The output of the above statement will be 55000
AVG Function
The AVG function returns the average of the values of a specific column of the numeric data type. For example, to compute the average of values of ‘Salary’ column of EMPOLYEE table the SELECT statement using AVG function is:
SELECT AVG (Salary) FROM EMPLOYEE
The output of the above statement will be 28200
MAX Function
The MAX function returns the largest value in the specific column of numeric type data. For example to find the maximum value in column ‘Salary’ of EMPLOYEE table the SELECT statement is written as
SELECT MAX (Salary) FROM EMPLOYEE
The output of the above statement will be 30000
MIN Function
The MIN function returns the lowest value in the specific column of numeric type data. For example to find the minimum value in the column ‘Salary’ of EMPOLYEE table the SELECT statement is written as
SELECT MIN (Salary) FROM EMPLOYEE
The output of the above statement will be 25000
