[Tip] Cumulated Total Column

[dr_gori]

Look at this SQL - shows the accumulated total of the column! (without using analytical function)

Select all

SELECT  
  B.SAL 
, SUM(A.SAL) ACUMULADO 
FROM  
  EMP A 
, EMP B 
WHERE A.ROWID <= B.ROWID 
GROUP BY B.ROWID, B.SAL

there is an example:

Select all

SQL> SELECT  
  2    B.SAL 
  3  , SUM(A.SAL) ACUMULADO 
  4  FROM  
  5    EMP A 
  6  , EMP B 
  7  WHERE A.ROWID <= B.ROWID 
  8  GROUP BY B.ROWID, B.SAL 
  9  / 
 
       SAL  ACUMULADO 
---------- ---------- 
       800        800 
      1600       2400 
      1250       3650 
      2975       6625 
      1250       7875 
      2850      10725 
      2450      13175 
      3000      16175 
      5000      21175 
      1500      22675 
      1100      23775 
       950      24725 
      3000      27725 
      1300      29025 
                29025 
 
15 rows selected. 
 
SQL> 

[leobbg]

Only for those who like to invent fashion .. but who does not like it? Complicating or uncomplicating .. there will another very nice analytical function ... which has the same effect.

Select all

SELECT A.SAL 
,      SUM( A.SAL ) OVER( ORDER BY A.SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ACUMULADO 
FROM   EMP A 
GROUP BY A.SAL