在SELECT中的IF语句SQL查询

我有下面的存储过程，可以正常工作。

CREATE DEFINER=`root`@`localhost` PROCEDURE `TxCountByDatePerod`(IN `fromdate` DATE, IN `todate` DATE, IN `department` VARCHAR(20), IN `status` VARCHAR(10), IN `ipg` VARCHAR(20))
SELECT d.dept_name,
       s.service_name,
       COUNT(*) AS all_row_count
FROM department AS d
INNER JOIN service s ON s.dept_id=d.dept_id
INNER JOIN transaction_detail t ON s.dept_id=t.dept_id
AND s.service_id=t.service_id
WHERE t.tx_begin_date BETWEEN fromdate AND todate
  AND (t.dept_id = department
       OR department ='null')
  AND (t.tx_status = status
       OR status ='null')
  AND (t.pg_name = ipg
       OR ipg ='null')
GROUP BY t.dept_id,
         t.service_id

上述SP是用MySQL编写的，用于获取特定部门的特定服务提供的给定持续时间内的事务数。一个事务可以是3个不同的事务状态（tx_statuscoulmn），它们分别是：完成（1），失败（-1），未完成（0）。

我想做的是获取下面报告的数据。

现在的问题是所有状态都分组为一行。我尝试使用下面的SQL来获取所需的数据，但是它给出了语法不正确的错误。

CREATE DEFINER=`root`@`localhost` PROCEDURE `TxCountByDatePerod`(IN `fromdate` DATE, IN `todate` DATE, IN `department` VARCHAR(20), IN `status` VARCHAR(10), IN `ipg` VARCHAR(20))
SELECT d.dept_name,
       s.service_name,
       COUNT(*) AS all_row_count,
       IF(t.tx_status = 1,
          SELECT COUNT(*)
          FROM transaction_detail
          WHERE tx_status = 1) AS successful_tx_count
FROM department AS d
INNER JOIN service s ON s.dept_id=d.dept_id
INNER JOIN transaction_detail t ON s.dept_id=t.dept_id
AND s.service_id=t.service_id
WHERE t.tx_begin_date BETWEEN fromdate AND todate
  AND (t.dept_id = department
       OR department ='null')
  AND (t.tx_status = status
       OR status ='null')
  AND (t.pg_name = ipg
       OR ipg ='null')
GROUP BY t.dept_id,
         t.service_id

请参见下面添加的行。

IF(t.tx_status = 1, SELECT COUNT(*) FROM transaction_detail WHERE tx_status = 1) AS successful_tx_count is added to the SP.

我怎样才能解决这个问题？