I need to find the sum value of quantity with respect to item_id grooup by cardno.
table structure is
id | column1 | column2 | column3 | item_id | quantity | cardno
1 | x | y | z | 1 | 32 | 1234
2 | a | b | c | 2 | 48 | 2345
3 | x | y | z | 1 | 33 | 1234
my query is 
$stockTransactionData = TableRegistry::getTableLocator()->get(‘Transactions’)->find(‘all’,array(‘conditions’=>array(‘Transactions.rgi_district_code=’."’".$rgi_district_code."’", ‘Transactions.rgi_block_code=’."’".$rgi_block_code."’", ‘Transactions.dealer_id=’."’".$dealer_id."’",‘Transactions.dateoftransaction>=’."’".$stockdatefrom."’",‘Transactions.dateoftransaction<=’."’".$stockdateto."’"), ‘group’=>array(‘Transactions.rationcard_no’), ‘recursive’=>-1));
$quan1 = $stockTransactionData->newExpr()->addCase([$stockTransactionData->newExpr()->eq(‘Transactions.item_id’ , 1)],[‘Transactions.liftedquantity’,0],[‘integer’, ‘integer’]);
$quan2 = $stockTransactionData->newExpr()->addCase([$stockTransactionData->newExpr()->eq(‘Transactions.item_id’ , 2)],[‘Transactions.liftedquantity’,0],[‘integer’, ‘integer’]);
$quan3 = $stockTransactionData->newExpr()->addCase([$stockTransactionData->newExpr()->eq(‘Transactions.item_id’ , 3)],[‘Transactions.liftedquantity’,0],[‘integer’, ‘integer’]);
$quan4 = $stockTransactionData->newExpr()->addCase([$stockTransactionData->newExpr()->eq(‘Transactions.item_id’ , 4)],[‘Transactions.liftedquantity’,0],[‘integer’, ‘integer’]);
$quan5 = $stockTransactionData->newExpr()->addCase([$stockTransactionData->newExpr()->eq(‘Transactions.item_id’ , 5)],[‘Transactions.liftedquantity’,0],[‘integer’, ‘integer’]);
$stockTransactionData->select(['dealerName','rationcard_no','cardtype_id','hhdUniqueId','familyName','dateoftransaction','liftedquantity','quan1'=>$stockTransactionData->func()->sum($quan1),'quan2'=>$stockTransactionData->func()->sum($quan2),'quan3'=>$stockTransactionData->func()->sum($quan3),'quan4'=>$stockTransactionData->func()->sum($quan4),'quan5'=>$stockTransactionData->func()->sum($quan5)]);
the result query is building—
SELECT
Transactions.dealerName AS Transactions__dealerName, Transactions.rationcard_no AS Transactions__rationcard_no, Transactions.cardtype_id AS Transactions__cardtype_id, Transactions.hhdUniqueId AS Transactions__hhdUniqueId, Transactions.familyName AS Transactions__familyName, Transactions.dateoftransaction AS Transactions__dateoftransaction, Transactions.liftedquantity AS Transactions__liftedquantity,
(SUM(CASE WHEN Transactions.item_id = :c0 THEN :param1 END)) AS quan1,
(SUM(CASE WHEN Transactions.item_id = :c2 THEN :param3 END)) AS quan2,
(SUM(CASE WHEN Transactions.item_id = :c4 THEN :param5 END)) AS quan3,
(SUM(CASE WHEN Transactions.item_id = :c6 THEN :param7 END)) AS quan4,
(SUM(CASE WHEN Transactions.item_id = :c8 THEN :param9 END)) AS quan5
FROM transactions Transactions
WHERE
(
Transactions.rgi_district_code=‘347’
AND Transactions.rgi_block_code=‘801765’
AND Transactions.dealer_id=‘5a3ce593-c2e4-4f01-95f7-4f470a5cc272’
AND Transactions.dateoftransaction>=‘2018-08-22’
AND Transactions.dateoftransaction<=‘2018-08-28’
)
GROUP BY Transactions.rationcard_no
but this is expected as -----
SELECT
Transactions.dealerName AS Transactions__dealerName, Transactions.rationcard_no AS Transactions__rationcard_no, Transactions.cardtype_id AS Transactions__cardtype_id, Transactions.hhdUniqueId AS Transactions__hhdUniqueId, Transactions.familyName AS Transactions__familyName, Transactions.dateoftransaction AS Transactions__dateoftransaction, Transactions.liftedquantity AS Transactions__liftedquantity,
(SUM(CASE WHEN Transactions.item_id = 1 THEN Transactions.liftedquantity END)) AS quan1,
(SUM(CASE WHEN Transactions.item_id = 2 THEN Transactions.liftedquantity END)) AS quan2,
(SUM(CASE WHEN Transactions.item_id = 3 THEN Transactions.liftedquantity END)) AS quan3,
(SUM(CASE WHEN Transactions.item_id = 4 THEN Transactions.liftedquantity END)) AS quan4,
(SUM(CASE WHEN Transactions.item_id = 5 THEN Transactions.liftedquantity END)) AS quan5
FROM transactions Transactions
WHERE
(
Transactions.rgi_district_code=‘347’
AND Transactions.rgi_block_code=‘801765’
AND Transactions.dealer_id=‘5a3ce593-c2e4-4f01-95f7-4f470a5cc272’
AND Transactions.dateoftransaction>=‘2018-08-22’
AND Transactions.dateoftransaction<=‘2018-08-28’
)
GROUP BY Transactions.rationcard_no
:c0 and :param2 these should convert into field value and fieldname
please help me on this. Your help and reply will be appreciated.