PL/SQL SUM() Function

Last Updated : 23 Sep, 2024

The SUM() function in PL/SQL is used to calculate the sum of the numeric column. It is an aggregate function that performs calculations on a set of values and returns a single value.

In this article, we will explore the syntax, usage, and examples of the PL/SQL SUM() function to help us understand its importance in database operations.

PL/SQL SUM() Function

The SUM() function is commonly used in database management systems like Oracle to add up the values of a particular numeric field. It is an essential part of the aggregate functions that help in summarizing large datasets.
The SUM() function is often used in scenarios where we need to sum values from multiple rows like calculating the total sales of the product or the total salary of the employees.

Syntax:

SELECT SUM(column_name) 
FROM table_name 
[WHERE condition];

Key Terms:

column_name: The column on which the sum operation is to be performed. It should contain numeric data.
table_name: The name of the table where the column resides.
WHERE condition: A filter to restrict which rows are included in the sum.

Create the Employees Table

The given SQL query creates an employees table with three columns: employee_id, name, and salary. The subsequent INSERT statements add five employee records to the table, with their respective IDs, names, and salaries.

Query:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT
);

INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John', 50000);
INSERT INTO employees (employee_id, name, salary)
VALUES (2, 'Sarah', 60000);
INSERT INTO employees (employee_id, name, salary)
VALUES (3, 'David', 55000);
INSERT INTO employees (employee_id, name, salary)
VALUES (4, 'Emily', NULL);
INSERT INTO employees (employee_id, name, salary)
VALUES (5, 'Steve', 45000);

Output:

employee_id	name	salary
1	John	50000
2	Sarah	60000
3	David	55000
4	Emily	NULL
5	Steve	45000

Explanation:

In the above table, we have created an employees table with columns: employee_id, name, and salary. Notice that Emily's salary is NULL, which will be ignored by the SUM() function.

Example 1: Calculating Total Salary (Ignoring NULL Values)

This example demonstrates how to calculate the total salary of all employees using the SUM() function, which automatically ignores NULL values. In the context of employee salary data, this ensures that any missing salary information does not affect the result.

Query:

SELECT SUM(salary) AS total_salary
FROM employees;

Output:

total_salary
210000

Explanation:

The SUM() function adds the salaries of all employees except Emily, whose salary is NULL. The total sum is 50000 + 60000 + 55000 + 45000 = 210000.

Example 2: Using SUM() with GROUP BY Clause

When we need to calculate the sum of the values for different groups in a dataset the SUM() function is often paired with the GROUP BY clause. The GROUP BY allows to categorize your data into the distinct groups based on the one or more columns and then apply aggregate functions like SUM() to the each group individually.

Query:

ALTER TABLE employees ADD department VARCHAR(20);

UPDATE employees SET department = 'Sales' WHERE employee_id IN (1, 2);
UPDATE employees SET department = 'HR' WHERE employee_id IN (3, 4, 5);

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

Output:

department	total_salary
HR	100000
Sales	110000

Explanation:

The salaries are grouped by department. In the HR department, the sum of salaries is 55000 (David) + 45000 (Steve) = 100000, while in the Sales department, the sum is 50000 (John) + 60000 (Sarah) = 110000. Emily’s NULL salary is ignored.

Example 3: Using SUM() with HAVING Clause

The HAVING clause is used to filter groups based on the result of an aggregate function like SUM(). Let’s filter out departments whose total salary is less than 100000.

Query:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

Output:

department	total_salary
Sales	110000

Explanation:

Only the Sales department is shown because its total salary (110000) is greater than 100000. The HR department is excluded because its total salary is 100000, which does not meet the filter condition.

Example 4: SUM() with INNER JOIN Clause

The SUM() function can be used in combination with the INNER JOIN to aggregate data across multiple tables. Consider the following example where we have two tables: customers and sales.

Query:

SELECT c.customer_name, SUM(s.amount) AS total_sales
FROM customers c
INNER JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_name;

Output:

customer_name	total_sales
Customer A	250
Customer B	200

Explanation:

The SUM() function adds up the sales amounts for each customer. Customer A has sales of 100 + 150 = 250, and Customer B has a total of 200.

Conclusion

The SUM() function in PL/SQL is an essential tool for the database developers and administrators when it comes to summarizing numeric data. It can be used in the combination with the other SQL clauses like GROUP BY and WHERE to perform the powerful data aggregation. Understanding how to use SUM() efficiently allows to generate valuable insights from the large datasets.

PLSQL | SQRT Function

gururaj69bn

Improve

Article Tags :

PL/SQL SUM() Function

PL/SQL SUM() Function

Create the Employees Table

Output:

Example 1: Calculating Total Salary (Ignoring NULL Values)

Output:

Example 2: Using SUM() with GROUP BY Clause

Output:

Example 3: Using SUM() with HAVING Clause

Output:

Example 4: SUM() with INNER JOIN Clause

Output:

Conclusion

Similar Reads