SELECT query with CASE condition and SUM()

问题:

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';

回答1:

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';

回答2:

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 -

回答3:

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';

回答4:

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" & "'"

回答5:

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.