Sunday, February 21, 2016

Get Running total in Db2

Using an aggregate function in SQL is pretty straightforward. However we sometimes need to have other columns in a table along with the corresponding aggregate we are trying to calculate. This is not possible if we use the functions in the traditional way using group by clause. 

Lets take an example. Below is the table BookSales. 

BookID DateOfSale SaleCnt
1001 2016-01-01 33
1001 2016-01-02 10
1001 2016-01-03 16
1002 2016-01-04 22
1001 2016-01-05 12
1001 2016-01-06 20
1002 2016-01-07 10
1001 2016-01-08 23
1002 2016-01-09 12
1002 2016-01-10 50
1001 2016-01-12 14
1001 2016-01-13 24


What I want to find is the running total for each book along with the dates. The result I am expecting is something like this

BookID DateOfSale SaleCnt RunningTot
1001 2016-01-01 33 33
1001 2016-01-02 10 43
1001 2016-01-03 16 59
1001 2016-01-05 12 71
1001 2016-01-06 20 91
1001 2016-01-08 23 114
1001 2016-01-12 14 128
1001 2016-01-13 24 152
1002 2016-01-04 22 22
1002 2016-01-07 10 32
1002 2016-01-09 12 44
1002 2016-01-10 50 94

Query

SELECT A*,
       SUM(SALECNT) OVER(PARTITION BY BOOKID
                         ORDER BY DATEOFSALE) AS RUNNINGTOT
FROM BOOKSALES A;

You could have the same result even if we change the display order using OrderBy clause in the query.

For Example

SELECT A*,
       SUM(SALECNT) OVER(PARTITION BY BOOKID
                         ORDER BY DATEOFSALE) AS RUNNINGTOT
FROM BOOKSALES A
ORDER BY BOOKID, DATEOFSALE DESC;

---------------------------- RESULTS IN ------------------------------

BookID DateOfSale SaleCnt RunningTot
1001 2016-01-13 24 152
1001 2016-01-12 14 128
1001 2016-01-08 23 114
1001 2016-01-06 20 91
1001 2016-01-05 12 71
1001 2016-01-03 16 59
1001 2016-01-02 10 43
1001 2016-01-01 33 33
1002 2016-01-10 50 94
1002 2016-01-09 12 44
1002 2016-01-07 10 32
1002 2016-01-04 22 22






No comments:

Post a Comment