Group functions in oracle

Group Functions: - These functions are built in function in oracle. They operate on group of rows and return one value for entire group.

To study these functions we use “employee” table as example.

Selecting employee table



They are divided as below:

1. AVG:- This function returns AVERAGE value for group of values passed as argument of any column with numeric datatype.
If we use “distinct” keyword before column name in argument then it will return average value for group of values passed as argument of any column excluding duplicate values.

Syntax
AVG ( [distinct] column name)

In above syntax “distinct” is keyword and “column name” is any column of table with numeric value.

Use of AVG function



Use of AVG function excluding duplicate salary



Use of AVG function with WHERE condition



Use of AVG function with WHERE condition



2. SUM:- This function returns TOTAL value for group of values passed as argument of any column with numeric datatype.
If we use “distinct” keyword before column name in argument then it will return total value for group of values passed as argument of any column excluding duplicate values.

Syntax
SUM ( [distinct] column name)

In above syntax “distinct” is keyword and “column name” is any column of table with numeric value.

Use of SUM function



Use of SUM function excluding duplicate salary



Use of SUM function with WHERE condition



Use of SUM function with WHERE condition



3. MAX:- This function returns MAXIMUM value from group of values passed as argument of any column with numeric datatype.

Syntax
MAX (column name)

In above syntax “column name” is any column of table with numeric value.

Use of MAX function



4. MIN:- This function returns MINIMUM value from group of values passed as argument of any column with numeric datatype.

Syntax
MIN (column name)

In above syntax “column name” is any column of table with numeric value.

Use of MIN function



5. COUNT:- This function returns TOTAL NUMBER OF value for group of values passed as argument of any column with numeric datatype.
If we use “distinct” keyword before column name in argument then it will return total value for group of values passed as argument of any column excluding duplicate values.

Syntax
COUNT ( [distinct] column name)

In above syntax “distinct” is keyword and “column name” is any column of table with numeric value.

Use of COUNT function



Use of COUNT function excluding duplicate salary



Use of COUNT function with WHERE condition



Use of COUNT function with WHERE condition



I hope this article will help you