PL/SQL is a procedural language designed for the Oracle Database management system. Oracle PL/SQL offers a number of aggregate functions that can be used to combine data from multiple rows into a single value.
The PL/SQL aggregate functions can be used to perform calculations on sets of data. These functions are often used with the GROUP BY clause in a SELECT query, to calculate values for each group of rows. Some common aggregate functions in PL/SQL include:
COUNT returns the number of rows in a result set
SELECT COUNT(*) FROM Employee;
SUM returns the sum of the values in a specified column
SELECT SUM(Salary) FROM Employee;
AVG returns the average value of a specified column
SELECT AVG(Salary) FROM Employee;
MIN returns the minimum value of a specified column
SELECT MIN(Salary) FROM Employee;
MAX returns the maximum value of a specified column
SELECT MAX(Salary) FROM Employee;
These functions can also be used in conjunction with the GROUP BY clause to perform calculations on subsets of data. For example:
SELECT Department, AVG(Salary) FROM Employee GROUP BY Department;
This query returns the average salary for each department in the Employee table.
Other aggregate functions include FIRST and LAST, which can be used to retrieve the first or last value from a group of rows. XMLAGG can be used to concatenate values from multiple rows into a single XML string.