Aggregate Functions
Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
The important Aggregate functions are :
Avg Sum Max Min Count Stddev Variance
AVG
AVG( ALL /DISTINCT expr)
Returns the average value of expr.
Example
The following query returns the average salary of all employees.
select avg(sal) “Average Salary” from emp;
Average Salary
------------------------
2400.40
SUM
SUM(ALL/DISTINCT expr)
Returns the sum value of expr.
Example
The following query returns the sum salary of all employees.
select sum(sal) “Total Salary” from emp;
Total Salary
------------------------
26500
MAX
MAX(ALL/DISTINCT expr)
Returns maximum value of expr.
Example
The following query returns the max salary from the employees.
select max(sal) “Max Salary” from emp;
Maximum Salary
------------------------
4500
MIN
MIN(ALL/DISTINCT expr)
Returns minimum value of expr.
Example
The following query returns the minimum salary from the employees.
select min(sal) “Min Salary” from emp;
Minimum Salary
------------------------
1200
COUNT
COUNT(*) OR COUNT(ALL/DISTINCT expr)
Returns the number of rows in the query. If you specify expr then count ignore nulls. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls. COUNT never returns null.
Example
The following query returns the number of employees.
Select count(*) from emp;
COUNT
------
14
The following query counts the number of employees whose salary is not null.
Select count(sal) from emp;
COUNT
------
12
STDDEV
STDDEV(ALL/DISTINCT expr)
STDDEV returns sample standard deviation of expr, a set of numbers.
Example
The following query returns the standard deviation of salaries.
select stddev(sal) from emp;
Stddev
-------
1430
VARIANCE
VARIANCE(ALL/DISTINCT expr)
Variance returns the variance of expr.
Example
The following query returns the variance of salaries.
select variance(sal) from emp;
Variance
-------
1430