Hi i have a table with data like this
image — ImgBB[
^]
what i need, is to
1. sum up all the records with PaymentCode = '01' and put it in a new column Named: CashSum.
2. sum up all the records with PaymentCode = 9 and keep in column PaymentAmount
but as you saw i have a LEFT JOIN with another table called tenderID, thats a table with some mapping so if record came with paymentcode = 9 then it will take some other data, thats why i used left join so if something in that table match it will go to PaymentAmount
image — ImgBB[
^]
this is my current sql query:
SELECT CASE
WHEN StoreID = 1 THEN 'CWM'
WHEN StoreID = 2 THEN 'CWD' END as accountcode,
tmptable.DocEntry AS DocEntry,
tmptable.PaymentCode AS PaymentCode,
--SUM(tmptable.PaymentAmount) AS PaymentAmount,
CAST(sum(tmptable.unitprice * tmptable.quantity) AS decimal(19, 2)) AS PaymentAmount,
tenders.sapcreditcard AS sapcreditcard,
tmptable.FileName AS FileName,
tmptable.ERP_PaymentProcessed,
tmptable.ERP_InvoiceProcessed
FROM
tmptable LEFT JOIN tenders ON tmptable.PaymentCode = tenders.postenderid
WHERE
tmptable.BPA_ProcessStatus = 'ITEMPROCESSED' AND tmptable.ERP_InvoiceProcessed = 'Y' and tmptable.ERP_PaymentProcessed = 'N'
GROUP BY DocEntry, PaymentCode, sapcreditcard,FileName, StoreID, ERP_PaymentProcessed, ERP_InvoiceProcessed
what is the best way to achieve what i need?
thank you.
What I have tried:
SELECT CASE
WHEN StoreID = 1 THEN 'CWM'
WHEN StoreID = 2 THEN 'CWD' END as accountcode,
tmptable.DocEntry AS DocEntry,
tmptable.PaymentCode AS PaymentCode,
--SUM(tmptable.PaymentAmount) AS PaymentAmount,
CAST(sum(tmptable.unitprice * tmptable.quantity) AS decimal(19, 2)) AS PaymentAmount,
tenders.sapcreditcard AS sapcreditcard,
tmptable.FileName AS FileName,
tmptable.ERP_PaymentProcessed,
tmptable.ERP_InvoiceProcessed
FROM
tmptable LEFT JOIN tenders ON tmptable.PaymentCode = tenders.postenderid
WHERE
tmptable.BPA_ProcessStatus = 'ITEMPROCESSED' AND tmptable.ERP_InvoiceProcessed = 'Y' and tmptable.ERP_PaymentProcessed = 'N'
GROUP BY DocEntry, PaymentCode, sapcreditcard,FileName, StoreID, ERP_PaymentProcessed, ERP_InvoiceProcessed