Aggregate Function

Download PDF
Advertisement

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.

  1. COUNT
  2. SUM
  3. AVG
  4. MAX
  5. 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:

Advertisement

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

 

SQL Joins

Advertisement