select id
     , sum(a.balance) as current
     , sum(b.balance) as previous
     , sum(a.balance) + sum(b.balance) as total
  from mytable as a
    join mytable as b on a.id = b.id
 where a.date between date('2018-01-01') and date('2018-05-01')
   and b.date < date('2018-01-01')
group by id;

请注意，您可能希望根据变量确定日期范围。

一种方法是使用一对计算聚合值的子查询。因此，可以使用子等式选择指定日期范围内的和以及该范围外的和。然后添加 coalesce 替换 null 有效金额（0）。

select
  customer_id,
  coalesce(current, 0),
  coalesce(previous, 0),
  coalesce(current, 0) + coalesce(previous, 0) total
from
(
  select
    customer_id,
    (
      select sum(balance)
      from balance b
      where b.customer_id = a.customer_id
        and b.date between '2018-01-01' and '2018-05-01'
      group by b.customer_id
    ) current,
    (
      select sum(balance)
      from balance c
      where c.customer_id = a.customer_id
        and c.date not between '2018-01-01' and '2018-05-01'
      group by c.customer_id
    ) previous
   from balance a
) c