Oracle Aggregate Functions

  1. Aggregate functions operate over set of values and return single value.
  2. Aggregate functions ignores the null values.
  3. Applied on group data to get summarized result.
  4. Always applied on table columns.

Oracle have following group functions(Aggregate):

Max(): This returns maximum value within a column.

Example:

SELECT MAX(COMM) FROM emp;

MIN(): This returns minimum value.
Example:

SELECT MIN(COMM) FROM emp;

AVG(): This returns average value.

Example:

SELECT AVG(COMM) FROM emp;

SUM: This returns total value on number datatype column.

Example:

SELECT SUM(COMM) FROM emp;

COUNT(): This returns number of rows in a table.
Example:

SELECT COUNT(*) FROM emp;

COUNT(column_name): This returns number of not null values within a column.

Example:

SELECT COUNT(comm) FROM emp;
SELECT COUNT(DISTINCT(DEPTNO)) FROM emp;

Note: In all database system, we are not allowed to use group functions in where clause.

Powered by k2schools