类似下面的代码应该可以工作：

SELECT SUM(sub.amount) + SUM(sub.fee) as total FROM (
  select SUM(amount) as amount,
  CASE
      WHEN count(amount) < 3 OR SUM(ABS(amount)) < 100 THEN -5
      WHEN (count(amount) >= 3 AND SUM(ABS(amount)) <= 5) And amount > 100 THEN -3
      WHEN count(amount) <= 6 AND SUM(ABS(amount)) <= 100 THEN 0
      ELSE 0
  END as fee
  # Note that the clause below assume your dt field is a string, remove the conversion if not needed
  from transactions GROUP BY MONTH(STR_TO_DATE(dt,'%m/%d/%Y'))
) sub

这项工作分2步进行：
1.创建按月分组的第一个SELECT。注意以下事项：
使用CASE选择正确的月费。使用绝对值的总和，不确定这是否是你所需要的。
1.将费用总额与金额总额相加

**假设：**这是根据提供的业务规则计算2020年的年度余额

步骤1.以cte_year_2020生成2020年月份（01~12）列表
步骤2.计算每月的借方交易笔数和借方交易总金额（如果当月没有借方交易则置0）
步骤3.应用业务规则计算月费
第四步：总结

with recursive cte_year_2020 (yr_month, yr_mo, n) as (
select cast('2020-01-01' as date), '2020-01', 1 
union all
select date_add(yr_month, interval 1 month), date_format(date_add(yr_month, interval 1 month),'%Y-%m'), n+1
  from cte_year_2020
 where n < 12),
cte_monthly_txn as (
select m.yr_mo                   as yr_mo,
       sum(if(coalesce(t.amount,0) < 0, 1, 0 ))        as debit_txn_count,
       sum(if(coalesce(t.amount,0) < 0, t.amount, 0 )) as debit_txn_amount,
       sum(if(coalesce(t.amount,0) > 0, t.amount, 0 )) as credit_txn_amount
  from cte_year_2020 m
  left
  join transactions t
    on m.yr_mo = date_format(t.dt,'%Y-%m')
 group by m.yr_mo),
cte_monthly_txn_fee as (
select yr_mo,
       debit_txn_count,
       debit_txn_amount,
       credit_txn_amount,
       case 
          when debit_txn_count < 3 or abs(debit_txn_amount)  < 100 then -5.0
          when debit_txn_count between 3 and 5 and abs(debit_txn_amount) > 100 then -3.0
          when debit_txn_count >=6 or abs(debit_txn_amount) >= 100 then 0.0
          else NULL
       end as fee       
  from cte_monthly_txn)
select sum(debit_txn_amount)  as debit,
       sum(credit_txn_amount) as credit,
       sum(fee)               as fee,
       sum(debit_txn_amount)  +
       sum(credit_txn_amount) +
       sum(fee)               as balance    
  from cte_monthly_txn_fee;

在汇总之前，cte_monthly_txn_fee中的数据如下：

yr_mo  |debit_txn_count|debit_txn_amount   |credit_txn_amount |fee|
-------+---------------+-------------------+------------------+---+
2020-01|              3|-118.00000095367432|               0.0| -3|
2020-02|              1|-3.5299999713897705|45.400001525878906| -5|
2020-03|              0|                0.0|               0.0| -5|
2020-04|              1| -62.16999816894531|               0.0| -5|
2020-05|              0|                0.0|               0.0| -5|
2020-06|              6| -295.6099967956543|               0.0|  0|
2020-07|              0|                0.0|               0.0| -5|
2020-08|              0|                0.0|               0.0| -5|
2020-09|              3|-113.66000127792358|               0.0| -3|
2020-10|              0|                0.0|             48.75| -5|
2020-11|              1|  -92.7699966430664|               0.0| -5|
2020-12|              3|-196.82000350952148|               0.0| -3|

总结后：

debit             |credit          |fee  |balance           |
------------------+----------------+-----+------------------+
-882.5599973201752|94.1500015258789|-49.0|-837.4099957942963|

不确定为什么预期输出为906.56？缺少什么？