I'm currently using these sql statements. My table has the field CPaymentType which contains "Cash" or "Check". I can sum up the amount of payments by executing 2 SQL statements as shown below. In this case, the user won't even notice the speed difference when executing 2 sql statements or just 1, however, I don't like my way, I just want 1 sql statement. How do I reconstruct these into 1 statement with CASE conditions? I can't figure it out since examples online result in either 1 or 0 or boolean. I don't want the postdated Check payments to be included. Thank you very much.
Select SUM(CAmount) as PaymentAmount
from TableOrderPayment
where CPaymentType='Cash' and CStatus='Active';
Select SUM(CAmount) as PaymentAmount
from TableOrderPayment
where CPaymentType='Check' and CDate<=SYSDATETIME() and CStatus='Active';
5 Answers
Select SUM(CASE When CPayment='Cash' Then CAmount Else 0 End ) as CashPaymentAmount,
SUM(CASE When CPayment='Check' Then CAmount Else 0 End ) as CheckPaymentAmount
from TableOrderPayment
Where ( CPayment='Cash' Or CPayment='Check' ) AND CDate<=SYSDATETIME() and CStatus='Active';
To get each sum in a separate column:
Select SUM(IF(CPaymentType='Check', CAmount, 0)) as PaymentAmountCheck,
SUM(IF(CPaymentType='Cash', CAmount, 0)) as PaymentAmountCash
from TableOrderPayment
where CPaymentType IN ('Check','Cash')
and CDate<=SYSDATETIME()
and CStatus='Active';
select CPaymentType, sum(CAmount)
from TableOrderPayment
where (CPaymentType = 'Cash' and CStatus = 'Active')
or (CPaymentType = 'Check' and CDate <= bsysdatetime() abd CStatus = 'Active')
group by CPaymentType
Cheers -
Use an "Or"
Select SUM(CAmount) as PaymentAmount
from TableOrderPayment
where (CPaymentType='Check' Or CPaymentType='Cash')
and CDate <= case CPaymentType When 'Check' Then SYSDATETIME() else CDate End
and CStatus='" & "Active" & "'"
or an "IN"
Select SUM(CAmount) as PaymentAmount
from TableOrderPayment
where CPaymentType IN ('Check', 'Cash')
and CDate <= case CPaymentType When 'Check' Then SYSDATETIME() else CDate End
and CStatus='" & "Active" & "'"
I don't think you need a case statement. You just need to update your where clause and make sure you have correct parentheses to group the clauses.
SELECT Sum(CAMount) as PaymentAmount
from TableOrderPayment
where (CStatus = 'Active' AND CPaymentType = 'Cash')
OR (CStatus = 'Active' and CPaymentType = 'Check' and CDate<=SYSDATETIME())
The answers posted before mine assume that CDate<=SYSDATETIME() is also appropriate for Cash payment type as well. I think I split mine out so it only looks for that clause for check payments.
