Subtract by batch members

Question

I have the following problem, I want to do the subtraction by batch, that is to say I will subtract the minimum value of a batch 'n_lot' against the other values ​​of the same batch I tried with the following query

select T_AO.N_AO, n_lot, montat_soum
    , montat_soum - min(montat_soum) over() as resultat
    , designation_entr
from t_soumission, t_entreprise, T_AO
where T_AO.N_AO='02/STG/2023' and t_soumission.cod_entr=t_entreprise.cod_entr
order by resultat

as it is shown in the image named "result 1" while I would like to obtain the result as shown in the image "result 2"

[enter image description here](https://i.stack.imgur.com/ejnnv.png)

thanks a lot

Answer 1

select
    montat_soum,
    montat_soum - min(montat_soum) over(partition by n_lot) as resultat,
    T_AO.N_AO,
    n_lot
from t_soumission
join T_AO
    on T_AO.N_AO='02/STG/2023'  
order by n_lot

I found the solution, if it helps anyone else