PL/SQL Aggregate Function

In PL/SQL, aggregate functions play an important role in summarizing and analyzing data from large datasets. These built-in SQL functions perform calculations on a set of values and return a single result, making them invaluable for tasks like calculating totals, averages, and identifying the highest or lowest values.

In this article, we will explain PL/SQL Aggregate Functions with the syntax, key components, and examples of PL/SQL aggregate functions. It also provides us the way how to use these PostgreSQL Functions effectively.

What are PL/SQL Aggregate Functions?

PL/SQL aggregate functions are designed to operate on multiple rows of data and return a single, summarized result. These functions are especially useful when working with large datasets because they reduce the data into meaningful insights. They work with numeric, string, and date values and are paired with the GROUP BY clause to perform calculations on specific subsets of data.

Some of the most commonly used PL/SQL aggregate functions include:

• SUM(): Calculates the total sum of values.
  
• COUNT(): Counts the number of rows.
  
• AVG(): Finds the average value.
  
• MAX(): Identifies the highest value.
  
• MIN(): Identifies the lowest value.

Syntax

SELECT AGGREGATE_FUNCTION(column_name_or_expression)
FROM table_name
[WHERE condition]
[GROUP BY column_name]
[HAVING condition];

Key Terms

• AGGREGATE_FUNCTION: The name of aggregate function for example SUM, COUNT, AVG, MIN, MAX.
  
• column_name_or_expression: The column or expression the function will be operate on.
  
• table_name: The name of table from which the data is selected.
  
• WHERE (optional): It is used to filter the rows before applying aggregation function.
  
• GROUP BY (optional): It is used to group the rows that have same values in the specified columns.
  
• HAVING (optional): It is used to filter groups after aggregation is applied.

Examples of PL/SQL Aggregate Function

Let us create an Employees table and insert sample values into it for use in our PL/SQL aggregate function examples. These examples will show how to apply various aggregate functions like SUM, AVG, and COUNT to this dataset.

Query:

CREATE TABLE employees (
    employee_id   NUMBER(10)     PRIMARY KEY,  -- Unique identifier for each employee
    first_name    VARCHAR2(20),                -- Employee's first name
    last_name     VARCHAR2(20),                -- Employee's last name
    department_id NUMBER(10),                  -- Department ID the employee belongs to
    salary        NUMBER(10, 2),               -- Employee's salary with 2 decimal places
    hire_date     DATE                         -- The date when the employee was hired
);

-- Insert the rows into employees table
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary, hire_date)
VALUES (1, 'John', 'Doe', 10, 50000, TO_DATE('2020-05-10', 'YYYY-MM-DD'));

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary, hire_date)
VALUES (2, 'Jane', 'Smith', 20, 60000, TO_DATE('2019-03-20', 'YYYY-MM-DD'));

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary, hire_date)
VALUES (3, 'Alice', 'Johnson', 10, 70000, TO_DATE('2021-07-15', 'YYYY-MM-DD'));

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary, hire_date)
VALUES (4, 'Bob', 'Brown', 20, 90000, TO_DATE('2022-01-10', 'YYYY-MM-DD'));

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary, hire_date)
VALUES (5, 'Charlie', 'Davis', 30, 40000, TO_DATE('2020-12-05', 'YYYY-MM-DD'));

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary, hire_date)
VALUES (6, 'Eve', 'Wilson', 10, 55000, TO_DATE('2018-09-25', 'YYYY-MM-DD'));

Output

Example 1: Using SUM and GROUP BY

The SUM function is used to calculate the total salary for each department. This query shows how much each department spends on salaries.

Query:

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Output

Explanation:

• This query calculates the total salary for each department by summing the salary values for all employees within the department.
  
• The result will be shown with department_id and total salary paid to all the employees in that department.

Example 2: Using AVG with GROUP BY

The AVG function finds the average salary for each department, which is useful for analyzing salary trends across departments.

Query:

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Output

Explanation:

• This query calculates the average salary for employees in each department by averaging the salary values for all employees in the department.
  
• The result will be shown with each department_id and average salary for the employees in that department.

Example 3: Using COUNT and MAX with GROUP BY

The COUNT function is combined with MAX to count the number of employees in each department and identify the highest-paid employee in each department.

Query:

SELECT department_id, COUNT(employee_id) AS num_employees, MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;

Output

Explanation:

• In this example, COUNT and MAX functions are used.
  
• This query counts number of employees (COUNT(employee_id)) and it find the highest salary (MAX(salary)) for each department.
  
• The result shows each department_id, the number of the employees and the highest salary in the each department.

Conclusion

PL/SQL aggregate functions are essential tools for summarizing and analyzing data in Oracle databases. They allow users to perform operations like summing, averaging, counting, and identifying the maximum or minimum values from a large set of rows.

These functions make it easier to derive meaningful insights from large datasets, and they are often paired with GROUP BY for more granular analysis. Whether we are analyzing trends, generating reports, or optimizing operations, PL/SQL aggregate functions offer powerful and efficient ways to work with your data.

jagan716

Follow

Improve

Article Tags :

• Databases
• PL/SQL