Showing posts with label Running total. Show all posts
Showing posts with label Running total. Show all posts

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