Sum of case statement

Question

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

I want to sum PaymentAmountCheck and PaymentAmountCash, can anyone help me please

You can not use an alias you defined in the same SELECT if that is what you mean — juergen d, Apr 18 '19 at 07:07
Possible duplicate of [SELECT query with CASE condition and SUM()](https://stackoverflow.com/questions/20935265/select-query-with-case-condition-and-sum). It is actually **EXACTLY** the same query. Same table, same columns, same everyhing — Joakim Danielson, Apr 18 '19 at 07:07
Can you do just one SUM where the IF statement's condition is: CPaymentType='Check' OR CPaymentType='Cash'? — Mihai, Apr 18 '19 at 07:09

score 0 · Answer 1 · answered Apr 18 '19 at 07:08

0

You can add those two fields with derived option like the below:

SELECT PaymentAmountCheck, 
      PaymentAmountCash, 
      PaymentAmountCheck + PaymentAmountCash AS TotalAmount
FROM (
 Select SUM(IIF(CPaymentType='Check', CAmount, 0)) as PaymentAmountCheck, 
       SUM(IIF(CPaymentType='Cash', CAmount, 0)) as PaymentAmountCash,
 from TableOrderPayment 
 where CPaymentType IN ('Check','Cash') and CDate<=SYSDATETIME() and CStatus='Active';
) AS Q;

Also in SQL Server, you may use IIF

answered Apr 18 '19 at 07:08

Arulkumar

12,966
14
47
68

hey I have used case in place of IIF and that's worked perfectly Thanks for the help but when I am trying to use more columns then it is giving the error – Varsha Babani Apr 18 '19 at 07:49
What is the error message? – Arulkumar Apr 18 '19 at 07:54
Done with it Thanks for the help. – Varsha Babani Apr 18 '19 at 07:58
If it solve your problem, you can [accept the answer](https://meta.stackexchange.com/a/5235/312043) – Arulkumar Apr 18 '19 at 08:42

score 0 · Answer 2 · answered Apr 18 '19 at 07:09

Since in the where clause you have mentioned CPaymentType you can just add sum(CAmount) in select that will give you sum of PaymentAmountCheck and PaymentAmountCash .

  Select SUM(IF(CPaymentType='Check', CAmount, 0)) as PaymentAmountCheck, 
           SUM(IF(CPaymentType='Cash', CAmount, 0)) as PaymentAmountCash ,
           sum(CAmount) total
    from TableOrderPayment 
    where CPaymentType IN ('Check','Cash') and CDate<=SYSDATETIME() and CStatus='Active';

Sum of case statement

2 Answers2