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