Mysql syntax error running total

Question

The Employee table holds the salary information in a year.

Write a SQL to get the cumulative sum of an employee's salary over a period of 3 months but exclude the most recent month.

The result should be displayed by 'Id' ascending, and then by 'Month' descending.

Example Input

| Id | Month | Salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |

Output

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |

Explanation Employee '1' has 3 salary records for the following 3 months except the most recent month '4': salary 40 for month '3', 30 for month '2' and 20 for month '1' So the cumulative sum of salary of this employee over 3 months is 90(40+30+20), 50(30+20) and 20 respectively.

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |

Employee '2' only has one salary record (month '1') except its most recent month '2'.

| Id | Month | Salary |
|----|-------|--------|
| 2  | 1     | 20     |

Employ '3' has two salary records except its most recent pay month '4': month '3' with 60 and month '2' with 40. So the cumulative salary is as following.

| Id | Month | Salary |
|----|-------|--------|
| 3  | 3     | 100    |
| 3  | 2     | 40     |

My query is

SELECT e.id, e.month,
       RunningTotal = sum(e.salary ) OVER (ORDER BY e.Id  ,e.month ) 
FROM employee e inner join
     employee e2
      on e.id=e2.id and e.month=e2.month and
         e.month <> (select max(month) from employee group by id having id=e.id )
order by e.id asc, e.month desc

Error I am getting is

Line 3: SyntaxError: near '(ORDER BY e.Id ,e.month ) FROM employee e inner join employee e2 on e.id=e2.i'

MySQL only supports window functions since version 8. That is probably the issue with your query. — Gordon Linoff, Jul 26 '18 at 16:52
I would suggest that you delete this question. Ask another qestion and provide sample data and desired results. — Gordon Linoff, Jul 26 '18 at 16:59
you need to provide structure of your tables to let us help you — Kunal, Jul 26 '18 at 17:00

score 0 · Answer 1 · answered Jul 26 '18 at 18:23

0

Try this simple query:

select id, month, salary,
       (select sum(salary) from tbl where id = t.id and month <= t.month) cumulative
from tbl t
where month <> (select max(month) from tbl where id = t.id)
order by id, month desc

answered Jul 26 '18 at 18:23

Michał Turczyn

32,028
14
47
69

@Nayyar if my answer solved your problem you should accept it and optionally upvote. – Michał Turczyn Jul 26 '18 at 21:10

Mysql syntax error running total

1 Answers1