Find Cumulative Salary of an Employee


  • 0

    Click here to see the full article post


  • 0
    V

    SELECT EMP.ID,
    EMP.MONTH,
    ( SELECT SUM (SALARY)
    FROM EMPLOYEE F
    WHERE F.ID = EMP.ID AND F.MONTH <= EMP.MONTH
    GROUP BY F.ID)
    AS SALARY
    FROM ( SELECT E.*
    FROM EMPLOYEE E,
    ( SELECT ID, MAX (MONTH) MX
    FROM EMPLOYEE
    GROUP BY ID) T
    WHERE E.ID = T.ID AND E.MONTH < T.MX
    ORDER BY E.ID, E.MONTH) EMP
    ORDER BY EMP.ID, EMP.MONTH DESC;


  • 0
    S

    SELECT E1.Id as id, E1.Month as month,Sum(E2.Salary) as salary
    FROM Employee E1, Employee E2
    where E1.id=E2.id
    and E1.Month >= E2.Month
    and E1.Month - E2.Month < 3
    and Concat(E1.Id,E1.Month) not in (Select Concat(Id,max(Month))as Conc from employee group by Id)
    group by E1.ID,E1.Month
    order by E1.ID,E1.Month Desc;


  • 0
    C

    SELECT 1.id AS id, e1.month AS month, Sum(e2.salary) AS salary
    FROM Employee e1 JOIN Employee e2
    ON e1.id = e2.id
    AND e1.month >= e2.month
    AND e1.month - e2.month < 3
    WHERE e1.id || e1.month NOT IN (
    SELECT id || max(month) FROM Employee GROUP BY id
    )
    GROUP BY e1.id, e1.month
    ORDER BY e1.id, e2.month DESC;


  • 0
    Y

    select e1.Id, e1.Month, SUM(e2.Salary) Salary
    from Employee e1,
    Employee e2
    where e1.Id = e2.Id
    and e2.Month < (select MAX(Month) from Employee)
    and e2.Month <= e1.Month
    and e1.Month < (select MAX(Month) from Employee)
    and e1.Month - e2.Month <= 3
    group by e1.Id, e1.Month
    order by e1.Id, e1.Month desc


  • 0
    N

    Is (IFNULL(E1.salary, 0) really necessary?


  • 0
    Z

    yes. it is necessary. since it not have IFNULL, the salary sum of months that do not have 2 months ahead will not be calculated.


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.