I need to find a way to query a calculation in which I subtract the result of a duplicate sample to the result of its parent sample. The duplicate and parent are grouped by the batch number. The duplicate will have a run of 2, whereas the parent will have a run of 1. I need to subtract both values, and then put the result of the difference in a new column that is in both parent/duplicate rows. Any clue on how to go about this? I have no experience subtracting from rows within the same table based on a grouping.

An example of what I am seeing:

An example of an output:

CodePudding user response：

You could use CTEs to store the child and parent values and then query such as:

with child as (
  select batch, result
  from my_table
  where run = 2
  ), 
parent as (
  select batch, result
  from my_table
  where run = 1
  )
select t.sample, t.result, t.batch, t.run, c.result - p.result as diff
from my_table t
left join child c
  on t.batch = c.batch
left join parent p
  on t.batch = p.batch

Output:

If a child is not found, then the diff column will display null.

CodePudding user response：

Oracle alternative using MODEL clause:

with cte (Sample    ,Result,    Batch,  Run)
    as (select  'S1A',  '20',   '5',    '1' from dual
union all   select  'S1B',  '50',   '5',    '2' from dual
union all   select  'S2A',  '10',   '6',    '1' from dual
union all   select  'S2B',  '30',   '6',    '2' from dual
)
, cte2 as (
select t1.*,0 as diff from cte t1
  )
  
  select sample,result,batch,run,diff from cte2
  model
partition by (batch)
dimension by (run)
measures (result, diff, sample)
rules (
  diff['1'] = result['2'] - result['1']
  ,diff['2'] = result['2'] - result['1']
  )

fiddle