CODEWITHSUNDEEP
sqloracle-database
zygotee
zygotee

Reputation: 13

Subtraction value with same table

I'm stuck with my query using Oracle, please help me.

This is my table:

id cust order qty value
0001 abc 10.000 2000 8000
0001 abc 10.000 4000 6000
0001 abc 10.000 4000 6000
0002 bcd 5000 2000 3000
0003 fgh 3000 1000 2000
0004 ghj 5000 NULL 5000

result that i want :

id cust order qty value
0001 abc 10.000 2000 8000
0001 abc 10.000 4000 4000
0001 abc 10.000 4000 0
0002 bcd 5000 2000 3000
0003 fgh 3000 1000 2000
0004 ghj 5000 NULL 5000

value = order - qty

So, the calculation based on same id and same cust, they are subtract from rows before, if id and cust does not same, they subtract normally.

Thanks in advance

Upvotes: 1

Views: 1635

Answers (3)

Prabhat Bhatt
Prabhat Bhatt

Reputation: 63

Seems like you are trying to partition by id and order by id and qty. You can try

Select id,  cust,   order,  qty,  order-coalesce(sum(qty) over (partition by id order by id,  qty),0) as value from myTable;

Upvotes: 0

MT0
MT0

Reputation: 168470

I am going to assume that you have another column which tells you the order in which the qty should be subtracted from the order column and have called this the datetime column (but you can use whatever column you have available to provide a deterministic ordering of the rows). Given that, you can calculate the value using an analytic SUM function:

SELECT id,
       cust,
       datetime,
       order_total,
       qty,
       order_total
         - SUM( COALESCE( qty, 0 ) )
             OVER ( PARTITION BY id, cust, order_total ORDER BY datetime )
         AS value
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( id, cust, datetime, order_total, qty ) AS
SELECT '0001',  'abc',  DATE '2021-01-01', 10000,   2000 FROM DUAL UNION ALL
SELECT '0001',  'abc',  DATE '2021-01-02', 10000,   4000 FROM DUAL UNION ALL
SELECT '0001',  'abc',  DATE '2021-01-03', 10000,   4000 FROM DUAL UNION ALL
SELECT '0002',  'bcd',  DATE '2021-01-01', 5000,    2000 FROM DUAL UNION ALL
SELECT '0003',  'fgh',  DATE '2021-01-01', 3000,    1000 FROM DUAL UNION ALL
SELECT '0004',  'ghj',  DATE '2021-01-01', 5000,    NULL FROM DUAL;

Outputs:

ID   | CUST | DATETIME  | ORDER_TOTAL |  QTY | VALUE
:--- | :--- | :-------- | ----------: | ---: | ----:
0001 | abc  | 01-JAN-21 |       10000 | 2000 |  8000
0001 | abc  | 02-JAN-21 |       10000 | 4000 |  4000
0001 | abc  | 03-JAN-21 |       10000 | 4000 |     0
0002 | bcd  | 01-JAN-21 |        5000 | 2000 |  3000
0003 | fgh  | 01-JAN-21 |        3000 | 1000 |  2000
0004 | ghj  | 01-JAN-21 |        5000 | null |  5000

db<>fiddle here

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

You can try the below - using lag()

select id,cust,orders,qty,
orders-qty-coalesce(lag(qty) over(partition by id,cust order by qty),0) as value
from tablename

OUTPUT:

id          cust        orders  qty    value
0001        abc         10000   2000    8000
0001        abc         10000   4000    4000
0002        bcd         5000    2000    3000
0003        fgh         3000    1000    2000

Upvotes: 0

Related Questions