[Hint] Cumulative Total column

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 05 Aug 2005 4:02 pm

See this sql-shows the cumulative total of column! (without using analytic function)

Code: 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
here's an example:
Code: 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>
Last edited by dr_gori on Thu, 15 Sep 2005 11:48 am, edited 1 time in total.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 02 Sep 2005 3:31 pm

Just for those who like to invent fashion. but who doesn't like? Complicated or uncomplicated. There goes another analytic function pretty cool ... which has the same effect.

Code: 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
leobbg
Location: PORTO ALEGRE - RS

Leo BBG Consultor Oracle


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 5 guests