CASE Statement with a SUM
<p>This is my table structure.</p>

<pre><code>===================================
main_section | currency | amount
===================================

Tender           INR        2000
Bank             USD        3000
Tender           INR        1500
Tender           INR        1850
===================================
</code></pre>

<p>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</p>

<pre><code>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)
</code></pre>