mysql-使用group by子句运行多个类别的总和

我有这张桌子:

CREATE TABLE tbl_sample
(
ID SERIAL PRIMARY KEY 
,sale decimal(10,2)
,pc varchar(45)
,trans_date date
);

 INSERT INTO tbl_sample
 VALUES 
 (1,100, 'p1','2019-08-27' ),(2,150, 'p2', '2019-08-27'),(3,175,'p1', '2019-08-27')
,(4,250, 'p2', '2019-08-28'),(5,100, 'p2', '2019-08-28'),(6,88,'p1', '2019-08-28')
,(7,120, 'p1', '2019-08-29'),(8,130,'p1', '2019-08-29'),(9,275,'p2', '2019-08-29');

此查询:

 select pc, trans_date, (select sum(x.sale) from tbl_sample x where x.id <= t.id and  t.pc = 
 x.pc) - sale as accum_sales_beginning, sale, (select sum(x.sale) from tbl_sample x where 
 t.pc = x.pc and x.id <= t.id) as accum_sales_ending from tbl_sample t  where t.trans_date 
 between '2019-08-27' and '2019-08-29' and t.pc = 'p1';

结果是:

  | pc | trans_date | accum_sales_beginning |  sale   | accum_sales_ending |
  --------------------------------------------------------------------------
  | p1 | 2019-08-27 |         0.00          |  100.00 |      100.00        |
  | p1 | 2019-08-27 |       100.00          |  175.00 |      275.00        |
  | p1 | 2019-08-28 |       275.00          |   88.00 |      363.00        |
  | p1 | 2019-08-29 |       363.00          |  120.00 |      483.00        |
  | p1 | 2019-08-29 |       483.00          |  130.00 |      613.00        |

现在,当我使用“group by”子句时:

  select t.pc, t.trans_date, (select sum(x.sale) from tbl_sample x where x.id <= t.id and  
  t.pc = x.pc) - sum(sale) as accum_sales_beginning, sum(sale), (select sum(x.sale) from 
  tbl_sample x where t.pc = x.pc and x.id <= t.id) as accum_sales_ending from tbl_sample t  
  where t.trans_date between '2019-08-27' and '2019-08-29' and t.pc = 'p1' group by 
  t.trans_date;

它给了我一个错误。我使用的是mysql 5.7,所以不能使用window函数或“sum()over”。 下面是我想要的输出。这可能吗?

  | pc | trans_date | accum_sales_beginning |  sale   | accum_sales_ending |
  --------------------------------------------------------------------------
  | p1 | 2019-08-27 |         0.00          |  275.00 |      275.00        |
  | p1 | 2019-08-28 |       275.00          |   88.00 |      363.00        |
  | p1 | 2019-08-29 |       363.00          |  250.00 |      613.00        |

此外,如果我将查询'2019-08-28'和'2019-08-29'之间的交易日期,这将是所需的输出:

  | pc | trans_date | accum_sales_beginning |  sale   | accum_sales_ending |
  --------------------------------------------------------------------------
  | p1 | 2019-08-28 |       275.00          |   88.00 |      363.00        |
  | p1 | 2019-08-29 |       363.00          |  250.00 |      613.00        |