August 11, 2022 at 2:05 pm
Hello everyone,
I am trying to calculate the field called "Load weight".
The calculation should be SUM(Weight) but not just the weight of the load that is calculated on this row but all the rows associated with this load.
For example:
SELECT AR_LOAD (item#), AR_LOAD_WK_DC (which is a concat of AR_LOAD, week and DC#) , WEIGHT (just weight of one AR_LOAD)
I thought in order to find me a grand total of the weight for designated filed I need to use Window Functions.
I thought it should look like this:
SUM(WEIGHT) OVER (PARTITION BY AR_LOAD_WK_DC ORDER BY WEIGHT) AS 'LOAD_WEIGHT'
However, for some reason it doesn't sum taking into consideration all the weights that fall under " AR_LOAD_WK_DC" bucket. It just returns the very same weight for my current AR_LOAD.
For example, for the AR_LOAD that I am working on right now. Its weight is 15. But the total weight of all weights that fall under AR_LOAD_WK_DC should be 25,190.00
Does someone know what I am doing wrong? Is window function cannot achieve this?
August 11, 2022 at 2:17 pm
Can you demonstrate this issue by providing some consumable test data?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 11, 2022 at 3:25 pm
My best guess is that you are using the wrong partition. Without data is hard to determine what the correct partition is, but I would hazard that you should be using AR_Load instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply