SELECT b.*, IFNULL(ob.debit, 0) oDebit, IFNULL(ob.credit, 0) oCredit, IFNULL(sum(a.amount_dr), 0) as currentyeardebit, IFNULL(sum(a.amount_cr), 0) as currentyearcredit, IFNULL((select ifnull(SUM(amount_dr), 0)-ifnull(SUM(amount_cr), 0) from comp_journal_detail aa3, comp_chart_of_acct ab3, _ref_acct_group d3 where aa3.companyID = b.companyID and aa3.acctcode = ab3.id and ab3.acctGroupID = d3.id and d3.acctTypeID = 3 and aa3.year <2015), 0) as lastRetainInc, IFNULL((select ifnull(SUM(amount_dr), 0)-ifnull(SUM(amount_cr), 0) from comp_journal_detail aa4, comp_chart_of_acct ab4, _ref_acct_group d4 where aa4.companyID = b.companyID and aa4.acctcode = ab4.id and ab4.acctGroupID = d4.id and d4.acctTypeID = 4 and aa4.year <2015), 0) as lastRetainExp, sum(a.amount_dr - a.amount_cr) as total, `b`.`acctCode`, `b`.`acctName`, `b`.`acctGroupID`, `d`.`acctTypeID`, `e`.`balance`, IFNULL((select sum(aa.amount_dr) from comp_journal_detail aa where aa.acctCode = b.ID and aa.year < 2016 And aa.year >= 2015), 0)+IFNULL(ob2.debit, 0) as lastyeardebit, IFNULL((select sum(aa.amount_cr) from comp_journal_detail aa where aa.acctCode = b.ID and aa.year < 2016 And aa.year >= 2015), 0)+IFNULL(ob2.credit, 0) as lastyearcredit
FROM (`comp_chart_of_acct` b)
LEFT JOIN `comp_journal_detail` a ON `a`.`acctCode` = `b`.`ID` and a.year = 2016 And a.period <= 0
LEFT OUTER JOIN `_ref_acct_group` d ON `d`.`ID` = `b`.`acctGroupID`
LEFT OUTER JOIN `comp_acct_type` e ON `e`.`ID` = `d`.`acctTypeID`
LEFT OUTER JOIN `comp_opening_balance` ob ON `ob`.`companyID` = `b`.`companyID` and ob.accountID = b.ID and ob.year =2016
LEFT OUTER JOIN `comp_opening_balance` ob2 ON `ob2`.`companyID` = `b`.`companyID` and ob2.accountID = b.ID and ob2.year =2015
WHERE `b`.`companyID` =  '3'
AND `b`.`acctGroupID` >  0
AND `b`.`id` > 0
GROUP BY `b`.`acctCode`