Simple Example How To Use Aggregate Function on Oracle SQL

Aggregate Function is an internal function of the Oracle Database to perform a calculation of the value. The values might have a data type of character, numeric or date. Aggregate functions typically used in conjunction with Group By syntax of grouping function.

There are several aggregate functions in Oracle DB, among other things:

AVG -> are used to calculate the average value of the collections of the data

MAX -> used to find the maximum value of the collections of the data

MIN -> used to find the minimum value of the collections of the data

COUNT -> used to count the number of rows from the collections of the data

SUM -> are used to calculate the total value of the collections of the data

If aggregate functions comes with some attributes, the attributes must be stated on Group By clause.

Suppose known tdata table as follows:

TGL Value

-----------------

10-JAN-15 70

11-FEB-15 80

18-JAN-15 70

18-JUL-15 60

10-FEB-15 90

15-FEB-15

If asked to show months along with the average value

MONTH AVG (VALUE)

-----------------------

JAN 70

FEB 85

JUL 60

it can be made the following statement:

SELECT TO_CHAR (date, 'MON') months, AVG (value)

FROM tdata

GROUP BY TO_CHAR (date, 'MON');