CODEWITHSUNDEEP
sqldatabaseoracle-database
just10
just10

Reputation: 57

How to pass the value of previous row to current row?

How can I pass the result of previous row to the computation of the current row

Given the unit and the cost, I need to get the average cost of each transactions:

The formula:

  1. Average cost is the sum of transaction cost
  2. If Type is Sub then Trx cost is equal to cost
  3. If Type is Red then Trx cost is Unit * (sum of previous trx cost/sum of previous units)
|  Row | Type | Unit | Cost | TrxCost  | Ave_cost |
|  1   |Sub   | 0.2  | 1000 |  1000    | 1000     |
|  2   |Sub   | 0.3  | 2500 |  2500    | 3500     |
|  3   |Sub   | 0.1  | 600  |  600     | 4100     |
|  4   |Red   |- 0.2 |-1100 | -1366.67 | 2733.33  |
|  5   |Sub   | 0.3  | 1000 |  1000    | 3733.33  |
|  6   |Red   | -0.6 | -600 | -3200    | 533.33   |

Update:

Order is based on row number.

Thanks.

Upvotes: 0

Views: 907

Answers (3)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

You may use Recursive CTE

WITH cte (row_num,
     type,
     unit,
     sum_of_unit,
     cost,
     trxcost,
     ave_cost
) AS (
     SELECT row_num,
            type,
            unit,
            unit AS sum_of_unit,
            cost,
            cost AS trxcost,
            cost AS ave_cost
     FROM t
     WHERE row_num IN (
          SELECT MIN(row_num)
          FROM t
     )
     UNION ALL
     SELECT t.row_num,
            t.type,
            t.unit,
            c.sum_of_unit + t.unit AS sum_of_unit,
            t.cost,
            CASE t.type
                 WHEN 'Sub'   THEN t.cost
                 WHEN 'Red'   THEN t.unit * ( c.ave_cost / c.sum_of_unit )
            END
       AS trxcost,
            c.ave_cost + CASE t.type
                 WHEN 'Sub'   THEN t.cost
                 WHEN 'Red'   THEN t.unit * ( c.ave_cost / c.sum_of_unit )
            END AS ave_cost
     FROM t
     JOIN cte c ON t.row_num = c.row_num + 1
)
SELECT * FROM cte

Dbfiddle Demo

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

You can do this in two passes: one to get theTrxCost, then one to get the Ave_cost.

What you are calling "average" is a running total by the way; you are merely adding up values.

You need window functions with ROWS BETWEEN clauses. (In case of SUM(...) OVER (ORDER BY ...) this is implicitly BETWEEN UNBOUNDED PRECEDING AND CURRENT, however).

select 
  id, type, unit, cost, round(trxcost, 2) as trxcost,
  round(sum(trxcost) over (order by id), 2) as ave_cost
from
(
  select 
    id, type, unit, cost,
    case 
      when type = 'Sub' then cost 
      else
        unit *
        sum(cost) over (order by id rows between unbounded preceding and 1 preceding) /
        sum(unit) over (order by id rows between unbounded preceding and 1 preceding)
    end as trxcost
  from mytable
)
order by id;

I renamed your row column id, because ROW is a reserved word.

The last row's results differ from yours. I used your formula, but get different figures.

Rextester demo: https://rextester.com/ASXFY4323

Upvotes: 1

Corion
Corion

Reputation: 3925

See the SQL Window Functions , which allow you to access values from other rows in the result set. In your case, you will need to tell us some more criteria for when to stop looking etc.:

select
    lag(unit,1) over (partition by type order by whatever) 
  * lag(cost,1) over (partition by type order by whatever)
from Trx

But I'm still missing how you want to correlate the transactions and the reductions to each other. There must be some column you're not telling us about. If that column (PartNumber?) is known, you can simply group by and sum by that.

Upvotes: 0

Related Questions