3

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)
Sanju Menon
  • 747
  • 1
  • 10
  • 24
  • 2
    The syntax is not correct its `sum(case when main_section = 'Tender' then amount else 0 end) as maintender1,...............` and if you have only 2 `main_section` then why not `select main_section,sum(amount) as amount group by main_section` – Abhik Chakraborty Mar 29 '16 at 07:38

1 Answers1

9

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
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • Its working fine. Can i add currency as well here. As of now its showing just amount. I need to get the currency information also. Do i need to give a seperate CASE statement just for currency? For example...SELECT SUM(CASE WHEN main_section = 'Tender' THEN amount,currency END) AS maintender1, – Sanju Menon Mar 29 '16 at 07:44
  • Is this possible or i do i need to add s seperate CASE statement to achieve the same? – Sanju Menon Mar 29 '16 at 08:09
  • @SanjuMenon You need two separate `CASE` expressions, one for `INR` and another one for `USD`. – Giorgos Betsos Mar 29 '16 at 08:13
  • No i didnt mean that. – Sanju Menon Mar 29 '16 at 08:18
  • CASE WHEN main_section = 'Bank' THEN currency END AS bankcurrency – Sanju Menon Mar 29 '16 at 08:19
  • @SanjuMenon Try `MAX(CASE WHEN main_section = 'Bank' THEN currency END) AS bankcurrency` – Giorgos Betsos Mar 29 '16 at 08:20
  • That is if main_section = Tender, then the CASE statement should show the amount and the currency. As of now its just showing the amount. – Sanju Menon Mar 29 '16 at 08:20
  • @SanjuMenon `CASE` is an **expression** not a **statement**. It always returns a *single scalar value*. You cannot use it to return two separate values. – Giorgos Betsos Mar 29 '16 at 08:22