Cumulative sum in MYSQL based on multiple columns-CodePudding

Let's say I have the following table:

CREATE TABLE sales(
                      id serial PRIMARY KEY,
                      sales_employee VARCHAR(50) NOT NULL,
                      fiscal_year INT NOT NULL,
                      sale DECIMAL(14,2) NOT NULL
);

INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
      ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);

How to I get the following result:

2016 Bob 100
2016 Alice 150
2016 John 200
2017 Bob 250
2017 Alice 250
2017 John 350
2018 Bob 450
2018 Alice 450
2018 John 600

The number of employees and years in real sample is much more.

CodePudding user response：

That's a window sum :

select s.*, sum(sale) over(partition by sales_employee order by fiscal_year) as running_sales
from sales s
order by fiscal_year, sales_employee