SELECT query with CASE condition and SUM()

Question

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

If you don't want post dated cheques in your results, your 2nd query appears to achieve that. What don't you like about it? — Dan Bracuk, Jan 05 '14 at 15:21
Yeah, I already achieved what I want. However, I don't like using 2 sql statements. I want these 2 to be merged into 1 statement with CASE conditions :) — chris_techno25, Jan 05 '14 at 15:23
btw: "case expression" is the more correct term. (An "expression" evaluates to a single value.) — Paul Maxwell, Apr 18 '19 at 07:00

score 86 · Accepted Answer · answered Jan 05 '14 at 15:26

86

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

answered Jan 05 '14 at 15:26

Mudassir Hasan

28,083
20
99
133

Thank you for all your answers, all are correct, but I'd choose this 1 since I was looking for something with the CASE statement. I've never used the CASE statement which is why I want to try this example. I just made 2 sql statements so my point would easily be understood. Thank you Sir! – chris_techno25 Jan 05 '14 at 15:34
@chris_techno25 In regards to [this](http://stackoverflow.com/review/suggested-edits/3732435) I suggest, since you are a new user, you go through [Does this amount to inconsistency in the edit review process?](http://meta.stackexchange.com/q/182419/221866) and [Clarification of edit rejection](http://meta.stackexchange.com/q/179655/221866), [meta] discussions. – Bleeding Fingers Jan 05 '14 at 16:02
Correct one if we want to use CASE statement. Thank you for making our results correct @mudassir-hasan – Saraz Dec 05 '21 at 05:03

score 6 · Answer 2 · answered Jan 05 '14 at 15:27

6

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 -

answered Jan 05 '14 at 15:27

simon at rcl

7,326
1
17
24

score 5 · Answer 3 · answered Jan 05 '14 at 15:25

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

score 1 · Answer 4 · answered Jan 05 '14 at 15:24

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

score 1 · Answer 5 · answered Jan 05 '14 at 15:26

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.

SELECT query with CASE condition and SUM()

5 Answers5

Linked

Related