1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| with data as ( select 1 as rn,'François' as name, 'Bleu' as color, 21 as delta, 200 as total from dual
union all
select 2 as rn,'Pierre', 'Rouge', 18, null from dual
union all
select 3 as rn,'Jacques', 'Bleu', 11, null from dual
union all
select 4 as rn,'Emilie', 'Bleu', 10, null from dual
union all
select 5 as rn,'Richard', 'Rouge', 11, null from dual
union all
select 6 as rn,'Christelle', 'Bleu', 21, null from dual
union all
select 7, 'Joan', 'Rouge', 19, null from dual
union all
select 8, 'Armelle', 'Bleu', 9, null from dual
union all
select 9, 'Stéphanie', 'Bleu', 24, null from dual
union all
select 10, 'Léa', 'Bleu', 4, null from dual
)
select name, color, delta, sumtotal - subtotal as result from (
select rn, name, color, delta, total,
sum(total) over(partition by color order by rn) as sumtotal,
sum(delta) over(partition by color order by rn) as subtotal
from data
where color = 'Bleu'
)
; |
Partager