Friday, January 2, 2015

Running Total in Oracle SQL query

Here is the sql query to do the running total for salary column from employee table

--Running Total Salary

SELECT
      EMPLOYEE_ID,
      FIRST_NAME,
      SALARY,
      SUM(SALARY) OVER (ORDER BY FIRST_NAME
                                          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT  ROW) Running_Total
FROM
      EMPLOYEES
ORDER BY
      FIRST_NAME

--Running Total Salary by department

SELECT
      DEPARTMENT_ID,

      FIRST_NAME,
      SALARY,
      SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID
                                               ORDER BY DEPARTMENT_ID, FIRST_NAME)  RUNNIG_TOTAL_BY_DEPT
FROM
      EMPLOYEES

--Running Total Salary by department name

SELECT
      DEPARTMENT_NAME,

      FIRST_NAME,
      SALARY,
      SUM(SALARY) OVER (PARTITION BY DEPARTMENT_NAME
                                              ORDER BY DEPARTMENT_NAME, FIRST_NAME) RUNNIG_TOTAL_BY_DEPT
FROM
      EMPLOYEES, DEPARTMENTS
WHERE
      EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
ORDER BY
      DEPARTMENT_NAME,
      FIRST_NAME

No comments:

Post a Comment