Aggregate Functions

Aggregate functions are used to perform queries based on groups of rows rather than on a single row. It’s a group functions which permits users to select review information from groups of rows. The list of group functions will given as follows.

There are the Following examples of the use these functions.

AVG(colname)

This is used for Calculates the average of the column name specified.
Example

SELECT AVG(sal) FROM EMP; Displays the average of the sal column.
MAX(colname)

Example

SELECT MAX(qty) FROM ORDPRD;
The given query displays the maximum quantity in the ORDPRD table.
The output will be:
MAX(QTY)
---------
293

MIN(colname)

Example
SELECT MIN(qty) FROM ORDPRD;
The given query shows the minimum quantity in the table.
The output will be:
MIN(QTY)
---------
2

COUNT(colname)

It displays the total number of column values for the column specified in the colname format. Discards any NULL values for that particular column.
SELECT COUNT(empno) FROM emp;
COUNT(*)
This function returns the total number of records in a table. Involves NULL values also.

Example

SELECT COUNT(*) FROM emp;

GROUP BY Clause
Group By clauses are used to group selected rows and return a single row of short information. The Expressions in the GROUP BY clause can hold any column name in which appears in the table. Let’s consider a condition such that the total number of employees in each department is to be listed out along with the department number.  The subsequent example describes this.

Example

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
The above statement would show the table

DEPTNO    COUNT(*)
--------        ---------   
10        3
20        5
30        6