CODEWITHSUNDEEP
sqloracle-databaseplsql
yocak123
yocak123

Reputation: 33

Sum up all values from a list PLSQL

I want to sum up all values from a INDEX BY TABLE list.

In the list there are values like this:

24000, 4500, 7890 and so on.

This is the code:

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
  emp_id NUMBER(5);
  emp_sal NUMBER(10);
  i NUMBER(10) := 100;
  counter NUMBER(10) := 0;
  counted NUMBER(10) := 0;

  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee; 
  e_id NUMBER(10);
  
BEGIN
  LOOP
    SELECT employee_id, salary INTO emp_id, emp_sal FROM employees WHERE employee_id = i;
      employeelist(emp_id) := emp_sal;
      i := i + 1; 
    EXIT WHEN emp_id = 110;
  END LOOP;
  
  
  e_id := employeelist.FIRST; 
  LOOP 
  counted := counted + employeelist(e_id); --This is where I want to sum all the things. 
  counter := counter + 1;
  EXIT WHEN employeelist.count > counter;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(counted); --The result should be displayed here but the only thing that get displayed is the last value of the list
  


END;

Upvotes: 0

Views: 910

Answers (2)

MT0
MT0

Reputation: 167902

Lets assume that you have an associated array and you want to sum up the values within it. And, lets also assume that the associative array's index is sparse.

So, if we have the test data:

CREATE TABLE employees ( employee_id, salary ) AS
SELECT 42, 24000 FROM DUAL UNION ALL
SELECT 93,  4500 FROM DUAL UNION ALL
SELECT 17,  7890 FROM DUAL;

(Note: the IDs do not go 1, 2, 3.)

then we populate the associative array with the code:

DECLARE
  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee;
BEGIN
  FOR r_emp IN ( SELECT employee_id, salary FROM employees )
  LOOP
    employeelist(r_emp.employee_id) := r_emp.salary;
  END LOOP;
END;
/

To perform the sum then we need to iterate over the associative array. This cannot be done in SQL as an associative array is a PL/SQL only data type so you have two options:

  1. Convert the PL/SQL associative array to a collection in the SQL scope and use an SQL statement to sum the values; or
  2. Iterate over the associative array in PL/SQL.

This is the second option and will use .FIRST and .NEXT(index) properties of the associative array to iterate over the sparse array (whereas, using a counter and incrementing by 1 will fail when the array is sparse):

DECLARE
  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee;
  
  employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
  counted     NUMBER(10) := 0;
BEGIN
  FOR r_emp IN ( SELECT employee_id, salary FROM employees )
  LOOP
    employeelist(r_emp.employee_id) := r_emp.salary;
  END LOOP;
  
  employee_id := employeelist.FIRST;
  LOOP
    EXIT WHEN employee_id IS NULL;
    counted := counted + employeelist(employee_id);
    employee_id := employeelist.NEXT( employee_id );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( counted );
END;
/

Which outputs:

36390

db<>fiddle here

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

You don't need PL/SQL Code, but SQL only ;

In order to see Sum and Count of whole set of data :

SELECT SUM(salary) AS total_salary, COUNT(*) AS total_person
  FROM employees 
 WHERE employee_id BETWEEN 100 AND 110

to see Sum and Count per each employee in the increasing order of their id values(through using Analytic Function):

SELECT employee_id, 
       SUM(salary) OVER (ORDER BY employee_id) AS total_salary,  
       COUNT(*) OVER (ORDER BY employee_id) AS total_person
  FROM employees 
 WHERE employee_id BETWEEN 100 AND 110
 ORDER BY employee_id

Upvotes: 0

Related Questions