Add new column based on conditions and the value difference of other column

All we need is an easy explanation of the problem, so here it is.

I think I might be overlooking something obvious here. I’m not asking someone to write the whole code for me, I just need a hint or a link to a similar case.
Thanks in advance…

My query:

select Client , ProductID, M_POS_TYPE AS Keep_or_Keep_in_Transit, Amount
FROM inventory_table inv_table
JOIN inventory_position inv_pos
ON inv_pos.ProductID=inv_table.ProductID
group by Client, ProductID, M_POS_TYPE, Amount

Add new column based on conditions and the value difference of other column

How can I add a new column that checks if the the subtraction of the values in the column:
Amount (for Keep and Keep in Transit) is different from 0 for same ProductID and Client?

Rows 5 and 6 is a perfect case.


Then, output.

Add a new column:

Add new column based on conditions and the value difference of other column

I have tried to use is conditionals, CASE statement, but how can I make sure it will calculate the difference for the same Client and ProductID?

I’m looking for a solution in the generic case, there are thousands of different ProductsIDs and Clients values in the table. I’m a bit stuck in this problem.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Here’s one option.

As you didn’t post test case, I used result of your query as a source (i.e. sample data) (that’s the test CTE):

SQL> with test (client, prodid, kkit, amount) as
  2    (select 'c1', 'it1234', 'Keep'        , 100 from dual union all
  3     select 'c1', 'fr1234', 'Keep_Transit', 56  from dual union all
  4     select 'c2', 'it1234', 'Keep'        , 30  from dual union all
  5     select 'c2', 'br1234', 'Keep_Transit', 0   from dual union all
  6     select 'c3', 'no1234', 'Keep'        , 5   from dual union all
  7     select 'c3', 'no1234', 'Keep_Transit', 4   from dual union all
  8     select 'c4', 'no1234', 'Keep'        , 0   from dual union all
  9     select 'c4', 'us1234', 'Keep_Transit', 2   from dual
 10    ),

diff CTE sums amount ("Keep" is considered to be positive, "Keep_Transit" negative) per client and product ID:

 11  diff as
 12    (select client, prodid,
 13       sum(case when kkit = 'Keep' then amount
 14                when kkit = 'Keep_Transit' then -amount
 15           end) as diff
 16     from test
 17     group by client, prodid
 18     having min(kkit) = 'Keep'
 19        and max(kkit) = 'Keep_Transit'
 20    )

Finally, outer join source data to diff and use case expression to display Y if there’s difference:

 21  select t.client, t.prodid, t.kkit, t.amount,
 22    case when d.diff is not null then 'Y' end diff
 23  from test t left join diff d on d.client = t.client
 24                             and d.prodid = t.prodid
 25  order by t.client, t.prodid;

---------- ------ ------------ ---------- -----
c1         fr1234 Keep_Transit         56
c1         it1234 Keep                100
c2         br1234 Keep_Transit          0
c2         it1234 Keep                 30
c3         no1234 Keep                  5 Y
c3         no1234 Keep_Transit          4 Y
c4         no1234 Keep                  0
c4         us1234 Keep_Transit          2

8 rows selected.


Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply