inner join (select custID, sum(myField) as Summary group by 1) SumInfo on myMainTable.custID = SumInfo.custID>I have an R&R report field called 'CUSMV' which is being summed into a report field called 'MVTOT'. The value is reset each time the CustNum field changes.
>SELECT > t.dtTrade_DT, > t.iOrder_Hdr_ID, > t.iTrade_ID, > t.sTrade_Type_CD, > t.sProduct_CD, > t.decTrade_Qty, > t.decTrade_Unit_Price, > t.decTrade_Qty * t.decTrade_Unit_Price AS Net_Amount, > t.decTrade_Cash_Balance, > t.decTrade_Product_Balance, > t.iTrade_Conf_No, > tp.sTP_Name1, > tp.iTrading_Partner_ID, > CASE > WHEN p.sCommodity_CD = 'G' THEN t.decTrade_Cash_Balance * p.decProduct_Ozconv > ELSE 0 > END AS goz, > CASE > WHEN p.sCommodity_CD = 'S' THEN t.decTrade_Cash_Balance * p.decProduct_Ozconv > ELSE 0 > END AS soz, > CASE > WHEN p.sCommodity_CD = 'P' THEN t.decTrade_Cash_Balance * p.decProduct_Ozconv > ELSE 0 > END AS poz, > CASE > WHEN p.sCommodity_CD = 'L' THEN t.decTrade_Cash_Balance * p.decProduct_Ozconv > ELSE 0 > END AS loz, > t.decTrade_Product_Balance * p.decProduct_Ozconv * c.decSpot_Price AS cusmv > FROM Trade t > JOIN Trading_Partner tp ON tp.iTrading_Partner_ID = t.iTrading_Partner_ID > JOIN Product p ON p.sProduct_CD = t.sProduct_CD > JOIN Commodity c ON c.sCommodity_CD = p.sCommodity_CD > > >>