SQL provides a set of built-in functions for producing a single value for an entire group.
These functions are called as Set functions or Aggregate functions. These functions can work on a normal result table or a grouped result table. If the result is not grouped then the aggregate will be taken for the whole result table.
1) COUNT (*)
COUNT returns the number of rows
To find the total number of employees
SELECT COUNT(*) FROM emp;
COUNT (*)
14
Note: It is possible to restrict the rows over which COUNT operates. To find the total number of CLERKS, use
SELECT COUNT (*) FROM emp WHERE job = 'CLERK';
To find the total number of CLERKS hired after '13-jan-81', say
SELECT COUNT (*) FROM emp
WHERE job = 'CLERK' AND hiredate > '13-jan-81';
When an aggregate function is used in a SELECT statement, column names cannot be used in SELECT unless GROUP BY clause is used.
2) SUM (col_name expression)
SUM returns the total of values present in a particular column or a number of columns linked together in the expression. All the columns, which form the argument to SUM, must be numeric only.
To find the sum paid as salary to all employees every month
SELECT SUM (sal) FROM emp;
SUM (SAL)
29025
To find the yearly compensation paid to all SALESMEN use
SELECT SUM (12*sal) FROM emp
WHERE job = 'SALESMAN';
3) AVG (col_nameexpression)
AVG is similar to SUM. AVG returns the average of a NUMBER of values. The restrictions, which apply on SUM also, apply on AVG.
To find the average salary all employees
SELECT AVG (sal) FROM emp;
AVG (SAL)
2073.21429
To find the average yearly compensation paid to SALESMEN, use
SELECT AVG (12*sal) FROM emp
WHERE job = 'SALESMAN';
4) MIN (col_nameexpression)
MIN returns the lowest of the values from the column. MIN accepts columns which are NON-NUMERIC too.
To find the minimum salary paid to any employee
SELECT MIN (sal) FROM emp;
MIN (SAL)
800
To list the employee who heads the list alphabetically, use
SELECT MIN (ename) FROM emp;
5) MAX (col_nameexpression)
MAX is the reverse of MIN. MAX returns the maximum value from among the list of values.
To find the maximum salary paid to any employee
SELECT MAX (sal) FROM emp;
MAX (SAL)
5000
