SELECT `a`.*, `d`.`acctTypeID`, IFNULL((select debit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2015), 0) as oDebit, IFNULL((select credit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2015), 0) as oCredit, IFNULL((select debit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2016), 0) as oDebitPNL, IFNULL((select credit from comp_opening_balance where companyID = a.companyID and accountID = a.ID and year =2016), 0) as oCreditPNL, sum(b.amountdr) as yeardebit, sum(b.amountcr) as yearcredit, IFNULL(sum(lastYr.amountdr), 0) as lastyeardebit, IFNULL(sum(lastYr.amountcr), 0) as lastyearcredit
FROM (`comp_chart_of_acct` a)
LEFT OUTER JOIN `view_comp_acc_det` b ON `a`.`ID` = `b`.`acctID` and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) <= 201601 and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) >= 201601
LEFT OUTER JOIN `view_comp_acc_det` lastYr ON `lastYr`.`acctID` = `a`.`ID` and CONCAT(b.year,if(b.period<=9, CONCAT("0",b.period),b.period)) < 201601 and lastYr.year >= 2015
LEFT OUTER JOIN `_ref_acct_group` d ON `d`.`ID` = `a`.`acctGroupID`
WHERE `a`.`companyID` =  '3'
GROUP BY `a`.`acctCode`
ORDER BY `a`.`acctCode`