Group Function or Aggregate Functions in Oracle

11:26:00 PM Rohit Kumar Verma 0 Comments

Group Function or Aggregate Functions:-

1. max()
2. min()
3. avg()
4. sum()
5. count(*)
6. count(column_name)
In all databases group functions are operated over number of values in a column and returns a single value.

1. max():-
It will return maximum values from a column
eg. sql> select max(sal) from emp;
output: 5400

2. min()
It will return minimum values from a column
eg. sql> select min(sal) from emp;
output: 800

3. avg()
It will return the average of number datatypes column
eg: sql> select avg(sal) from emp;
output: 2266.07143

In oracle by default all group functions ignores null values except count(*) function . if we want to count the null values we must use nvl() function.

eg. sql>select avg(nvl(comm)) from emp;
output: 157.142

4. sum()
it will return the sum of number datatypes column
eg. sql> select sum(sal) from emp;

5. count(*)
It will return the number of rows in a table including null values
eg. sql> select count(*) from emp;
output: 14

6. count(sal)
It will return the no. of not null values in a column.
eg. sql> select count(comm) from emp;
output: 4

eg. sql> select count(distinct(deptno)) from emp;
output: 4