Click here to Skip to main content
14,973,001 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 5-May-21 17:36pm

1 solution

In general you can have functions inside an aggregation so if I understand the question correctly, you can use for example case structure to make the decision if to sum or not to sum a single value.

Consider the following simplified example
SQL
SELECT 
   SUM( CASE 
           WHEN Table1.PaymentCode = 1 THEN Table1.UnitPrice * Table1.Quantity
           ELSE 0
        END) AS Colummn1,
   SUM( CASE 
           WHEN Table1.PaymentCode = 9 THEN Table2.OtherUnitPrice * Table2.OtherQuantity
           ELSE 0
        END) AS Colummn2
FROM ...
   
Comments
Alexis Gaitan 6-May-21 11:49am
   
thank you so much
Wendelius 6-May-21 12:00pm
   
Glad to be of service :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900