oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Oracle Aggregate SQL Functions

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

 

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved