Cumulative sum for individuals in a new column in mysql

Question

I was trying to find the cumulative value on a table with employee_name and salary it is 10 years data ..... so how can i find cumulative sum for each individuals .... not just adding adding all the employees ......

For Eg :

  EMPLOYEE   SALARY
  JAMES      100
  JAMES      100
  JAMES      100
  SAM        100
  SAM        100
  SAM        400

This is my table:

I was trying to make it as:

EMPLOYEE   SALARY  CUMULATIVE_SUM
JAMES       100     100
JAMES       100     200
JAMES       100     300
SAM         100     100 
SAM         100     200
SAM         400     600

How can I get a result like this?

I tried but i end up getting the total for all the employees — joey, Mar 28 '14 at 02:05
Do you have a column that indicates the ordering of the records, such as an auto-incremented id or date? — Gordon Linoff, Mar 28 '14 at 02:49
Possible duplicate of [Cumulative sum over a set of rows in mysql](https://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql) — Lukas Eder, Sep 17 '18 at 18:12

score 1 · Answer 1 · answered Mar 28 '14 at 02:37

1

Can do if you add a salary date. Take the following table:

mysql> select * from salaries;
+-------+-------------+--------+
| name  | salary_date | amount |
+-------+-------------+--------+
| james | 2014-01-01  | 100.00 |
| james | 2014-02-01  | 100.00 |
| james | 2014-03-01  | 100.00 |
| sam   | 2014-01-01  | 400.00 |
| sam   | 2014-02-01  | 400.00 |
| sam   | 2014-03-01  | 400.00 |
+-------+-------------+--------+
6 rows in set (0.00 sec)

Make following query:

select e1.name, e1.salary_date, e1.amount,
(select sum(e2.amount) 
    from salaries e2 
where e2.name = e1.name and e2.salary_date <= e1.salary_date) as cumulated   
from salaries e1;

And the result is:

+-------+-------------+--------+-----------+
| name  | salary_date | amount | cumulated |
+-------+-------------+--------+-----------+
| james | 2014-01-01  | 100.00 |    100.00 |
| james | 2014-02-01  | 100.00 |    200.00 |
| james | 2014-03-01  | 100.00 |    300.00 |
| sam   | 2014-01-01  | 400.00 |    400.00 |
| sam   | 2014-02-01  | 400.00 |    800.00 |
| sam   | 2014-03-01  | 400.00 |   1200.00 |
+-------+-------------+--------+-----------+

Tested with mysql. Good luck :-)

answered Mar 28 '14 at 02:37

pmoo

321
1
3

That is most likely going to perform _terribly_ for any large-sized data set. Each person's section is going to take O(N^2) where N is the number of salary entries they have. It could be optimized in theory, but I highly doubt mysql would. – yshavit Mar 28 '14 at 02:41
Sure! It's much faster to do that cumulated salary by simple C++/JAVA/whatever code. – pmoo Mar 28 '14 at 02:50
Actually, the situation isn't that bad: is O(N^2) for each employee, where N is the number of months worked in the company, i.e. 120 for a 10 years working guy. It will not blow up if the company has 1000 employees! – pmoo Mar 28 '14 at 02:56
Your calculation is exactly the same one I mentioned. :) But I think it will still be quite slow. On my laptop -- admittedly, not a beefy server -- a data set of 100 people with 120 entries each took about half a minute. So I may have spoken too strongly when I said it'd perform terribly, but this probably isn't a query you'll want to run every time you serve a page! – yshavit Mar 28 '14 at 03:11
That said, your answer _does_ produce the requested output, and it's not actually as bad as I thought on gut instincts. I'm out of votes for the day, but when I get more I'll +1 your answer. – yshavit Mar 28 '14 at 03:26

score 0 · Answer 2 · answered Mar 28 '14 at 02:12

0

You need to use GROUP BY to group the table by person, and then you can use aggregating functions like AVG. This only gives you one sum per person, though (not the intermediate values, as in your example).

mysql> CREATE TABLE people(id int, salary int);
mysql> INSERT INTO people VALUES (1, 1), (1, 10), (1, 100),
                                 (2, 2), (2, 20), (2, 200);

mysql> SELECT id, SUM(salary), AVG(salary) FROM people GROUP BY id;
+------+-------------+-------------+
| id   | sum(salary) | avg(salary) |
+------+-------------+-------------+
|    1 |         111 |     37.0000 |
|    2 |         222 |     74.0000 |
+------+-------------+-------------+
2 rows in set (0.00 sec)

answered Mar 28 '14 at 02:12

yshavit

42,327
7
87
124

i dont need to group it ..i need to have a new column with added up values for each individual – joey Mar 28 '14 at 03:53
If you don't need the intermediate results, grouping is the way you get those added-up values. Note the `sum(salary)` and `avg(salary)` columns, which contain exactly that info. `sum(salary)` in the first row contains the total sum of salaries for person 1, for instance. – yshavit Mar 28 '14 at 04:17
please check the desired output table i have added in the question – joey Mar 28 '14 at 04:21
Do you actually need the intermediate values, or do you just want the grand total? I already mentioned that you won't get the intermediate values with my approach. Is there something you need that I'm not providing? If you need the intermediate sums, pmoo's answer is probably as good as you'll get. – yshavit Mar 28 '14 at 04:24
ya thanks .... i need the intermediate values ... i'll follow pmoo's answer ..... but i think it will run terrible for huge data sets/ ? – joey Mar 28 '14 at 04:29
The critical thing for its performance is not how big the data set is, but how many entries each person has. If you have 1,000 people, each with just 2 entries, it'll go pretty fast; if you have 2 people each with 1,000 entries, it'll go slow. You _may_ be able to write a stored procedure to do it more efficiently, but I'm not familiar with those. – yshavit Mar 28 '14 at 04:32
If you find pmoo's answer helpful, don't forget to accept it! :) – yshavit Mar 28 '14 at 04:42
There should be a check mark next to their answer; click it to turn it green. – yshavit Mar 28 '14 at 05:11

Cumulative sum for individuals in a new column in mysql

2 Answers2