I have this piece of SQL where I want to sum pv.[ACCOUNTINGCURRENCYAMOUNT] by some variables that are contained in the table pv. I have tried to put in the group by clause in different places in the code, with no correct result. Can anyone point me in the right direction as to where I should place the group by clause.
Thanks in advance!
UPDATE s SET s.ProjId = CASE WHEN d.PROJECT = 'N/A' OR d.PROJECT = '009021' THEN ISNULL((SELECT TOP 1 ProjId FROM [dbo].[tmpProjTransPostingVouchers] pv WHERE s.SUBLEDGERVOUCHER = pv.VOUCHER AND s.DATAAREA = PV.OPTdataareaid Sgroup by ProjId Having s.[ACCOUNTINGCURRENCYAMOUNT] = sum(pv.[ACCOUNTINGCURRENCYAMOUNT]) pvgroup by ProjID, VOUCHER, OPTDATAAREAID, LEDGERDIMENSION),'')
ELSE d.PROJECT END
FROM stLedgerTrans s inner join dDimensionUnion d on s.LEDGERDIMENSION = d.CombinationId INNER JOIN [dbo].[eLedgerPostingType] lp ON s.POSTINGTYPE = lp.ENUMITEMVALUE AND lp.PostingType like '%proj%'