Thursday, September 6, 2012

MySQL Using SUM and CASE in statement


Problems i faced in mysql query

one day i was working on Race project where director can payment online and manually .
I had been stored all Amount in one column "amt".
take a look on below image

MySQL Using SUM and CASE in statement

I was want SUM of manual payment , electronic payment and total of all payment in one query.  That day i had been write following query by using Mysql SUM() function and conditional CASE statement.
Here we need to use conditional CASE statement within a SUM() That's it.
And the query is

SELECT  SUM(CASE WHEN `pay_type`="manual" THEN `amt` ELSE 0 END) AS manual_amt,SUM(CASE WHEN `pay_type`="electronic" THEN `amt` ELSE 0 END) AS electronic_amt,SUM(`amt`) AS total_amt FROM pay

In cakephp you can sum like below
 $condition = array('fields' => array('Message.read_status AS read_status','SUM(CASE WHEN read_status=0 THEN \'1\' ELSE \'0\' END) AS MsgCnt'));

Perhaps it can happen with you so use my query and enjoy! .

2 comments:

  1. Nice article good one keep it up...

    ReplyDelete
  2. Thank you that helped me a lot :)

    Did this:
    SELECT SUM(CASE WHEN `car`="car1" THEN `money` ELSE 0 END) AS CAR1,SUM(CASE WHEN `car`="car2" THEN `money` ELSE 0 END) AS CAR2,SUM(`money`) AS total FROM rent WHERE date between '2015-01-01' AND '2015-12-31'

    Now just implemet output to json perhaps and som nice jquery charts :)

    ReplyDelete