Help with syntax for summing a columns having distinct values in another column

Database

Help with syntax for summing a columns having distinct values in another column

Hi, I'm new here and hoping to find some help.

My query currently retrieves 'last_thirteen_wk_sales' by 'ITEM_ID' and 'LOCN_NBR'.

I want to change it so it will SUM 'last_thirteen_wk_ sales' by 'ITEM_ID' and 'SRV_DC LOCN_NBR'.

LOCN_NBR AND SRV_DC LOCN_NBR is a "many to one" relationship so the result that I am looking will return far fewer records that what it does currently summing on LOCN_NBR.

I'm thinking this involves the 'DISTINCT' or 'UNIQUE' keyword but I'm not clear on the syntax. Thanks in advance...

SELECT

a.SYS_DATE,

a.KSN_ID,

a.ITEM_ID,

a.DVSN_NBR,

a.CAN_CARR_MDL_ID,

a.NETWORK_DISTRB_CD,

a.KM_DAY70_CHK_STR_QTY,

a.LOCN_NBR,

a.SERV_DC_LOCN_NBR,

b.last_thirteen_wk_sales

from

(SELECT

DATE as SYS_DATE,

T1.KSN_ID as  KSN_ID,

T1.ITEM_ID as ITEM_ID,

T1.DVSN_NBR as DVSN_NBR,

T2.CAN_CARR_MDL_ID as CAN_CARR_MDL_ID,

T2.NETWORK_DISTRB_CD as NETWORK_DISTRB_CD,

T3.KM_DAY70_CHK_STR_QTY as KM_DAY70_CHK_STR_QTY,

T4.LOCN_NBR as LOCN_NBR,

T4.SERV_DC_LOCN_NBR as SERV_DC_LOCN_NBR

from

Dataview.ksn T1,

Dataview.item T2,

Dataview.Item_store_count_c T3,

Dataview.item_locn_replen T4

--oi_merchT3,

where

T1.KSN_ID IN

 (

 2981558

 )

AND

T1.ITEM_ID = T2.ITEM_ID

AND

T1.ITEM_ID = T3.ITEM_ID

AND

T1.ITEM_ID = T4.ITEM_ID

) a  

--JOIN a and b

   Join

   (

   SELECT

   J1.KSN_ID as KSN_ID,

   J1.LOCN_NBR as LOCN_NBR,

   Sum (SELL_AMT)  last_thirteen_wk_sales

   FROM

   Dataview.SALES_WK_CALNDR_C J1

   WHERE

          date - CALNDR_WK_END_DT<= 91 

    GROUP BY

    1,2) b

     ON

   (a.KSN_ID = b.KSN_ID and a.LOCN_NBR = b.LOCN_NBR )

group by 1,2,3,4,5,6,7,8,9,10

order by 3 ASC;