SQL Sum Total with multiple assignments-CodePudding

select dc_id, whse_id, assg_id, START_DTIM,
    UNIT_SHIP_CSE*prod_cub as TOTAL_CUBE
from exehoust.aseld

I attached a photo to show how the query currently populates. I want to sum the TOTAL_CUBE for each distinct ASSG_ID. I have tried case where sum and group by but keep failing. Basically want to do a SUM IF for each distinct ASSG_ID

CodePudding user response：

You need to group by the assg_id, but ou need also the define what happens to all the other columns i choose MIN only to give you a hint, you need to choose the function yourself

select MIN(dc_id), MIN(whse_id), assg_id, MIN(START_DTIM),
    SUM(UNIT_SHIP_CSE*prod_cub) as TOTAL_CUBE
from exehoust.aseld
GROUP BY assg_id

CodePudding user response：

use select assg_id, sum() over(partition by assg_id order by assg_id) to sum by groupings