Group Functions in Oracle SQL

  3shTech (trainings@trishtechnology.com)

String Functions

Numeric Functions

Date Functions

Conversion Functions

Analytical Functions

Group Functions

·       Group Functions are the functions which process a set of rows and return a single value.

·       Group Functions are also called as aggregate functions (or) summary functions.

·       Group functions will be applied on all the rows and produces single output.

·       Group functions are mainly used to fetch summary information.

Types:-

·       SUM

·       AVG

·       MAX

·       MIN

·       COUNT

Note:

1) Max, Min and Count functions work with any data type.

2) Sum and Avg functions work only with Number data types.
 
 
SUM
 

·       This will give the sum of the values to the specified column.

Syntax: – SUM (column)
 

Example:–
SQL>SELECT SUM (sal) FROM EMP;

SQL>SELECT deptno, SUM (sal) FROM EMP GROUP BY deptno;

 
 
AVG:-
 

·       This will give the Average value 

Syntax: – AVG (column)

SQL>SELECT AVG(sal) FROM emp WHERE deptno=20;

 
 
TRUNC
 

·       This will give the average of the values of the specified column.

Syntax: – TRUNC (column)

Example:–
SQL>SELECT TRUNC (Avg (sal), 2) FROM emp;

SQL>SELECT deptno, TRUNC (Avg (sal), 2) FROM emp GROUP BY deptno; 

 
 
MAX

·       This will give the maximum of the values of the specified column.

Syntax: – MAX (column)

Example:–
 SQL>SELECT MAX (sal) FROM emp;

 SQL>SELECT deptno, MAX (sal) FROM emp GROUP BY deptno;

 
 
MIN
 

·       This will give the minimum of the values of the specified column.

Syntax: – MIN (column)
 

Example:–
 SQL>SELECT MIN (sal) FROM emp;

 SQL>SELECT deptno, MIN (sal) FROM emp GROUP BY deptno;

 
 
COUNT
 

·       This will give the count of the values of the specified column.

Syntax: – COUNT (column)

Example:–
 SQL>SELECT COUNT (*) FROM emp;

 SQL>SELECT deptno, COUNT (*) FROM emp GROUP BY deptno;

 

Hope you have gained some knowledge!!

Thank you!! 😊