I have this table:
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');
This query:
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';
has a result:
| 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 |
Now, when I used “group by” clause:
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;
It gives me an error. I am using mysql 5.7, so window function or “sum() over” cannot be used. Below is my desired output. Would this be possible?
| 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 |
Also, if I will query with trans_date between ‘2019-08-28’ and ‘2019-08-29’, this will be the desired output:
| 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 |
Advertisement
Answer
Try this below code
SELECT t.pc, t.trans_date, ( SELECT SUM(x.sale) FROM tbl_sample x WHERE x.id <= MAX(t.id) AND t.pc = x.pc -- Just used MAX(t.id) in place of t.id -- As group by is available in the query ) - SUM(sale) AS accum_sales_beginning, SUM(sale), ( SELECT SUM(x.sale) FROM tbl_sample x WHERE t.pc = x.pc AND x.id <= MAX(t.id) -- Just used MAX(t.id) in place of t.id -- As group by is available in the query ) 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.pc,t.trans_date;