Recipe 7.15. Changing Values in a Running TotalProblemYou want to modify the values in a running total depending on the values in another column. Consider a scenario where you want to display the transaction history of a credit card account along with the current balance after each transaction. The following view, V, will be used in this example: create view V (id,amt,trx) as select 1, 100, 'PR' from t1 union all select 2, 100, 'PR' from t1 union all select 3, 50, 'PY' from t1 union all select 4, 100, 'PR' from t1 union all select 5, 200, 'PY' from t1 union all select 6, 50, 'PY' from t1 select * from V ID AMT TR -- ---------- -- 1 100 PR 2 100 PR 3 50 PY 4 100 PR 5 200 PY 6 50 PY The ID column uniquely identifies each transaction. The AMT column represents the amount of money involved in each transaction (either a purchase or a payment). The TRX column defines the type of transaction; a payment is "PY" and a purchase is "PR." If the value for TRX is PY, you want the current value for AMT subtracted from the running total; if the value for TRX is PR, you want the current value for AMT added to the running total. Ultimately you want to return the following result set: TRX_TYPE AMT BALANCE -------- ---------- ---------- PURCHASE 100 100 PURCHASE 100 200 PAYMENT 50 150 PURCHASE 100 250 PAYMENT 200 50 PAYMENT 50 0 SolutionDB2 and OracleUse the window function SUM OVER to create the running total along with a CASE expression to determine the type of transaction: 1 select case when trx = 'PY' 2 then 'PAYMENT' 3 else 'PURCHASE' 4 end trx_type, 5 amt, 6 sum( 7 case when trx = 'PY' 8 then -amt else amt 9 end 10 ) over (order by id,amt) as balance 11 from V MySQL, PostgreSQL, and SQL ServerUse a scalar subquery to create the running total along with a CASE expression to determine the type of transaction: 1 select case when v1.trx = 'PY' 2 then 'PAYMENT' 3 else 'PURCHASE' 4 end as trx_type, 5 v1.amt, 6 (select sum( 7 case when v2.trx = 'PY' 8 then -v2.amt else v2.amt 9 end 10 ) 11 from V v2 12 where v2.id <= v1.id) as balance 13 from V v1 DiscussionThe CASE expression determines whether the current AMT is added or deducted from the running total. If the transaction is a payment, the AMT is changed to a negative value, thus reducing the amount of the running total. The result of the CASE expression is seen below: select case when trx = 'PY' then 'PAYMENT' else 'PURCHASE' end trx_type, case when trx = 'PY' then -amt else amt end as amt from V TRX_TYPE AMT -------- --------- PURCHASE 100 PURCHASE 100 PAYMENT -50 PURCHASE 100 PAYMENT -200 PAYMENT -50 After evaluating the transaction type, the values for AMT are then added to or subtracted from the running total. For an explanation on how the window function, SUM OVER, or the scalar subquery creates the running total see recipe "Calculating a Running Total." |