CASE Statement with a SUM

Question

This is my table structure.

===================================
main_section | currency | amount
===================================

Tender           INR        2000
Bank             USD        3000
Tender           INR        1500
Tender           INR        1850
===================================

Iam trying to do a CASE statement in which if 'main_section=tender' it should return the tender amount, if its bank it should return the bank amount. If there are more than one record, then it should sum and return the amount ( Here Tender has more than one record). Can anyone help me with this. The following is the case statement which i tried

CASE sum(com_payments.main_section 
         WHEN com_payments.main_section = 'Tender' 
         THEN main_tender = com_payments.amount
END AS maintender1)
CASE sum(com_payments.main_section 
         WHEN com_payments.main_section = 'Bank' 
         THEN main_bank = com_payments.amount
END AS mainbank1)

Answer 1

You have to do conditional aggregation:

SELECT SUM(CASE WHEN main_section = 'Tender' THEN amount END) AS maintender1,
       SUM(CASE WHEN main_section = 'Bank' THEN amount END) AS mainbank1      
FROM mytable