CODEWITHSUNDEEP
oracle-databaseoracle11g
lalachka
lalachka

Reputation: 413

get a sum on each row that adds the sum from previous row

i need to get a sum on each row that also adds the total from the previous row

here's the sample data

    Fax         Counts
    2063504752  1
    2080906666  1
    7180930966  2
    7182160901  1
    7182232046  1
    7182234134  1
    7182234166  0
    7182248132  2
    7182390090  1
    7182395285  0
    7185462234  0
    7185465013  1
    7185465281  1
    7185466029  0



here's the desired output

Fax         Counts  Totals
2063504752  1   1
2080906666  1   2
7180930966  2   4
7182160901  1   5
7182232046  1   6
7182234134  1   7
7182234166  0   7
7182248132  2   9
7182390090  1   10
7182395285  0   10
7185462234  0   10
7185465013  1   11
7185465281  1   12
7185466029  0   12

so i'd like to take the first row, record result in Totals column (1)

go to the second row, add the Counts from that row (1) to the Totals from previous row (1), record result in Totals (2)

go to the third row. add the Counts from that row (2) to the Totals from previous row (2), record result in Totals (4)

go to the fourth row. add the Counts from that row (1) to the Totals from previous row (4), record result in Totals (5)

and so on

I know the LAG function will read the value from a previous row but i can't code the first 2 rows, it seems like a circular reference.

this is as far as i got. it's only summing the last 2 rows, i can't add the total of the previous values

WITH T AS
       (SELECT 2063504752 fax
           ,1 counts
          FROM DUAL
        UNION ALL
        SELECT 2080906666
           ,1
          FROM DUAL
        UNION ALL
        SELECT 7180930966
           ,2
          FROM DUAL
        UNION ALL
        SELECT 7182160901
           ,1
          FROM DUAL
        UNION ALL
        SELECT 7182232046
           ,1
          FROM DUAL
        UNION ALL
        SELECT 7182234134
           ,1
          FROM DUAL
        UNION ALL
        SELECT 7182234166
           ,0
          FROM DUAL
        UNION ALL
        SELECT 7182248132
           ,2
          FROM DUAL
        UNION ALL
        SELECT 7182390090
           ,2
          FROM DUAL
        UNION ALL
        SELECT 7182395285
           ,0
          FROM DUAL
        UNION ALL
        SELECT 7185462234
           ,0
          FROM DUAL
        UNION ALL
        SELECT 7185465013
           ,1
          FROM DUAL
        UNION ALL
        SELECT 7185465281
           ,1
          FROM DUAL
        UNION ALL
        SELECT 7185466029
           ,0
          FROM DUAL)
SELECT fax
      ,counts
      ,counts + LAG(counts, 1) OVER (ORDER BY fax) Counts1
  FROM T

Upvotes: 0

Views: 2295

Answers (1)

Multisync
Multisync

Reputation: 8797

Use analytic functions:

select OtherID, Counts, sum(Counts) over(order by OtherID) Totals
from your_table;

sum(Counts) over(order by OtherID) tells Oracle to apply the sum to all the rows from the "begining" (according to the ORDER BY) until current row.

Analytic functions are like aggregate functions, but produce results for each row. In addition, you can tell Oracle which rows (windows) must be used to calculate the result for each row.

Upvotes: 3

Related Questions