SQL Aggregate Functions

Aggregate Functions summarizes the Result of an over a number of Rows in a table and give the result like one row. Actually Aggregate Function mostly used in Group by Function ,it means sum of salary, min of salary,max of salary,count of students,average of salary from each department.

The syntax of Aggregate Function are.

Syntax:

SELECT AGGREGATE FUNCTION(NUMBER OF ROWS RESULT) FROM TABLE_NAME;

We have mainly five types of aggregate functions.

1) MIN

2) MAX

3) SUM

4) AVG

5)COUNT

MIN AGGREGATE FUNCTION

 It is used to find the minimum value of the Column of table.

 Example:

SQL>SELECT MIN(SALARY_OF_EMPLOYEE) RESULT FROM EMPLOYEE_DETAILS;

          

RESULT

-----------

6000

MAX AGGREGATE FUNCTION

 It is used to find the maximum value of the column in a table.

Example:

SQL>SELECT MAX(SALARY_OF_EMPLOYEE) RESULT FROM EMPLOYEE_DETAILS;

          

RESULT

-----------

12000

SUM AGGREGATE FUNCTION

It is used to find the sum of the groups like department, or sum of the salary column of a table, finding the sum of the each department.

Example:

SQL>SELECT SUM(SALARY_OF_EMPLOYEE) RESULT FROM EMPLOYEE_DETAILS;

          

RESULT

-----------

112000

AVG AGGREGATE FUNCTION

here we are finding the average of the column values of table.

Example:

SQL>SELECT AVG(SALARY_OF_EMPLOYEE) RESULT FROM EMPLOYEE_DETAILS;

          

RESULT

-----------

8000

COUNT AGGREGATE FUNCTION

In this aggregate function we are finding the number of rows in column of a table.

Example:

SQL>SELECT  COUNT(SALARY_OF_EMPLOYEE) RESULT FROM EMPLOYEE_DETAILS;

          

RESULT

-----------

10



Top Searching Topics


Different types of indexes in oracle with examples