Saturday, November 5, 2016

Simple Example How To Use Aggregate Function on Oracle SQL

Leave a Comment
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');
Read More