CODEWITHSUNDEEP
mysqlsqlaggregate-functions
peter
peter

Reputation: 35

mysql - running sums for multiple categories with group by clause

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        |

Upvotes: 2

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

In MySQL 5.7, the most efficient method uses variables:

select trans_date, (accum_sales_ending - sales) as accum_sales_beginning, sales, accum_sales_ending
from (select trans_date, sales, (@s := @s + sales) as accum_sales_ending
      from (select s.trans_date, sum(s.sale) as sales
            from tbl_sample s
            where s.pc = 'p1'
            group by s.trans_date
            order by s.trans_date
           ) s cross join
           (select @s := 0) params
     ) s
where s.trans_date between '2019-08-28' and '2019-08-29' ;

Here is a db<>fiddle.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

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;

Upvotes: 1

Related Questions