Grand total of the sum of a field taking another field

  • 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?

     

  • Can you demonstrate this issue by providing some consumable test data?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply