March 13, 2020 at 3:58 pm
Hello all,
First of all, thank you for dwelling on my problem. It is as follows:
I want to decrement a total from quantity according to a specific dimension and criterion.
An example is better than a thousand words
I have the following data (see attachment):
First Name Color Sub Total Pieces
François Bleu 21 200
Pierre Rouge 18
Jacques Bleu 11
Emilie Bleu 10
Richard Rouge 11
Christelle Bleu 21
What I want is to remove, from my total 200, decrement step by step, which would give the following result:
First name Color Under Expected result Total pieces
François Bleu 21 179 200
Pierre Rouge 18 null
Jacques Bleu 11 168
Emilie Bleu 10 158
Richard Rouge 11 null
Christelle Bleu 21 137
the money from blue people and I want toFor now, I have the following request
Select TotalPièces - sum(Sous) OVER(PARTITION BY Prénom ORDER BY ...) as "Résultat attendu" From Table
I can't figure out how to ignore the red people. Thank you in advance for your help.
March 13, 2020 at 4:16 pm
Do you have a column that defines the order of those rows? If not, how do you know to subtract Pierre's number before Jacques's? I think you want to do something like this:
SUM(
CASE
WHEN Color <> 'Rouge' THEN Sous
ELSE 0
END
)
OVER (
PARTITION BY Prenom, Couleur
ORDER BY OrderColumn
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
That's totally off the top of my head, so make sure you test and tweak if necessary.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply