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
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! .
