CODEWITHSUNDEEP
oracle11g
Steven
Steven

Reputation: 15318

Build a SQL with sum

Here is my table - PK is (Con_num, version, order) :

Con_num version operation amount
15      1       A         1
15      1       B         2
15      1       C         3
15      2       A         4
15      3       A         5
15      3       B         6
15      4       C         7

I would like to have the total amount per version. The tricky part is that: for version 1, i just have to sum the amount. But for version 2, I need to sum the version 2 line (with operation = A) and to take the two lines from version 1 (with operation != A). Therefore, for version 3, i will take the two lines of version 3, and only the line with operation = C from version 1. Any new operation invalidate the one from the previous versions.

The result will be:

Con_num version amount
15      1       6     (1 + 2 + 3)
15      2       9     (4 + 2 + 3)
15      3       14    (5 + 6 + 3)
15      4       18    (5 + 6 + 7)

How can I do that ?

Upvotes: 0

Views: 42

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

For each con_num and version add up all records

  • for the same con_num
  • with no version greater than the version in question
  • having the highest version per operation

To get the amount of the record with the highest version can be solved with Oracle's KEEP FIRST/LAST:

select 
  base.con_num, 
  base.version,
  (
    select sum(max(mytable.amount) keep (dense_rank last order by mytable.version))
    from mytable
    where mytable.con_num = base.con_num
    and mytable.version <= base.version
    group by mytable.con_num, mytable.operation
  ) as total
from (select distinct con_num, version from mytable) base;

Upvotes: 3

Noel
Noel

Reputation: 10525

Using LAST_VALUE analytic function.

select con_num, version, q1+q2+q3
from (
    select x.*, 
    last_value(case when operation = 1 then amount end) ignore nulls over (order by version) q1,
    last_value(case when operation = 2 then amount end) ignore nulls over (order by version) q2,
    last_value(case when operation = 3 then amount end) ignore nulls over (order by version) q3
    from x
)
group by con_num,version, q1, q2, q3
order by con_num,version;

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35623

select
  Con_num, version, orderno, a0+a1+a2 as amount
from (
    select
          Con_num, version, orderno
        , lag(amount,2) over(partition by Con_num order by version, orderno) a2
        , lag(amount,1) over(partition by Con_num order by version, orderno) a1
        , amount a0
        , row_number() over(partition by Con_num, version order by orderno desc) as rn
    from table1
    ) d
where rn = 1

You seem to want only the "most recent" combinations of (Con_num, version, orderno) which can be identified using row_number() and the values required established using lag(,1) and lag(,2) but I don't reach the stated result.

result:

| con_num | version | orderno | amount |
|---------|---------|---------|--------|
|      15 |       1 |       3 |     37 |
|      15 |       2 |       1 |     42 |
|      15 |       3 |       2 |     35 |

sqlfiddle example

Upvotes: 0

Related Questions