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 lt  SYSDATETIME   and CStatus  Active

User · Answer

select CPaymentType  sum CAmount  from TableOrderPayment where  CPaymentType    Cash  and CStatus    Active   or  CPaymentType    Check  and CDate  lt   bsysdatetime   abd CStatus    Active   group by CPaymentType   Cheers -

User · Answer

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 lt  SYSDATETIME    and CStatus  Active

User · Answer

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 lt  SYSDATETIME      The answers posted before mine assume that CDate lt  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

User · Answer

Use an  Or   Select SUM CAmount  as PaymentAmount  from TableOrderPayment  where  CPaymentType  Check  Or CPaymentType  Cash      and CDate  lt   case CPaymentType When  Check  Then SYSDATETIME   else CDate End    and CStatus     amp   Active   amp        or an  IN   Select SUM CAmount  as PaymentAmount  from TableOrderPayment  where CPaymentType IN   Check    Cash      and CDate  lt   case CPaymentType When  Check  Then SYSDATETIME   else CDate End    and CStatus     amp   Active   amp

User · Answer

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 lt  SYSDATETIME   and CStatus  Active

[sql] SELECT query with CASE condition and SUM()

Examples related to sql

Examples related to sql-server

Examples related to sum

Examples related to case

Examples related to conditional-statements