CODEWITHSUNDEEP
oracle-databaseloopsplsqlsum
sunu dioh
sunu dioh

Reputation: 97

(Oracle) How to get sum value inside loop?

I need value of sum inside loop.

DECLARE
VAR_PCT NUMBER;
CURSOR C1 IS 
SELECT A, B FROM TBL;
BEGIN
FOR REC1 IN C1
  LOOP
--This where i need the value of sum(rec1.b) to calculate VAR_PCT:=(REC1.B/SUM(REC1.B))*100
   DBMS_OUTPUT.PUT_LINE(REC1.A ||'|'|| REC1.B ||'|'||VAR_PCT)
END LOOP;
END;

So, I basically need to figure out how to get the sum of B.

EDIT:

I forgot that I have one more variable that accumulate before the sum

*FOR REC1 IN C1
      LOOP*
    VAR_X := VAR_X+REC1.B
    *--This where i need the value of sum(rec1.b) to calculate VAR_PCT:=(REC1.B/SUM(REC1.B))*100
       DBMS_OUTPUT.PUT_LINE(REC1.A ||'|'|| REC1.B ||'|'||VAR_PCT)
    END LOOP;*

And the VAR_PCT is value for (VAR_X/SUM(REC1.B))*100

That's why I need calculate it inside the loop.

Upvotes: 2

Views: 3556

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

You can use window function sum(col) over () to find the overall total in the cursor definition itself. This is usually more performant then doing thing procedurally in a loop.

DECLARE
    CURSOR C1 IS 
        SELECT A, B, 100 * b / sum(b) over () VAR_PCT FROM TBL;
BEGIN
    FOR REC1 IN C1
    LOOP
        DBMS_OUTPUT.PUT_LINE(REC1.A ||'|'|| REC1.B ||'|'||REC1.VAR_PCT)
    END LOOP;
END;

If the sum is for each A, then use partition by:

DECLARE
    CURSOR C1 IS 
        SELECT A, B, 100 * b / sum(b) over (partition by A) VAR_PCT FROM TBL;
BEGIN
    FOR REC1 IN C1
    LOOP
        DBMS_OUTPUT.PUT_LINE(REC1.A ||'|'|| REC1.B ||'|'||REC1.VAR_PCT)
    END LOOP;
END;

Upvotes: 2

Related Questions