Analytical SQL - Running Total - Order by without Partition By

Welcome to the third post on the series Analytical SQL. In the first two posts we studied 

In this post we will create a report showing a running total of the complete EMP table in order of HIREDATE. To create a running total for the complete dataset, PARTITION BY is not required, we just need to add ORDER BY in OVER analytic clause
SQL> select d.deptno, d.dname, empno, ename, hiredate, sal,
  2  sum(sal) over(order by hiredate) running_total
  3  from emp e, dept d
  4  where e.deptno = d.deptno;

    DEPTNO DNAME               EMPNO ENAME      HIREDATE         SAL RUNNING_TOTAL
---------- -------------- ---------- ---------- --------- ---------- -------------
        20 RESEARCH             7369 SMITH      17-DEC-80     800.75        800.75
        30 SALES                7499 ALLEN      20-FEB-81     1600.9       2401.65
        30 SALES                7521 WARD       22-FEB-81       1250       3651.65
        20 RESEARCH             7566 JONES      02-APR-81     2975.5       6627.15
        30 SALES                7698 BLAKE      01-MAY-81     2850.3       9477.45
        10 ACCOUNTING           7782 CLARK      09-JUN-81    2450.45       11927.9
        30 SALES                7844 TURNER     08-SEP-81    1500.95      13428.85
        30 SALES                7654 MARTIN     28-SEP-81    1250.05       14678.9
        10 ACCOUNTING           7839 KING       17-NOV-81    5000.15      19679.05
        20 RESEARCH             7902 FORD       03-DEC-81     3000.6      23630.64
        30 SALES                7900 JAMES      03-DEC-81     950.99      23630.64
        10 ACCOUNTING           7934 MILLER     23-JAN-82       1300      24930.64
        20 RESEARCH             7788 SCOTT      19-APR-87    3000.55      27931.19
        20 RESEARCH             7876 ADAMS      23-MAY-87    1100.01       29031.2
As you can see in above output, data is ordered by HIREDATE, and RUNNING_TOTAL column is calculating SUM from the first row till the current row.

11 comments:

  1. Excellent Article!!! I like the helpful information you provide in your article.

    BA 3rd year result 2022

    ReplyDelete
  2. Thanks for the informative Content. I learned a lot here. Keep sharing more like this.
    AWS and Salesforce
    Salesforce Integrations

    ReplyDelete
  3. 25% OFF on Oracle Apps R12 Financials Self Paced Course along with 11 Additional Add On Courses (321 Session Videos of 120 Hours Recordings). Our Top Trending Course with 1700 Enrolled Udemy Students

    Please Check https://www.oracleappstechnical.com for details

    ReplyDelete
  4. Thanks for this blog keep sharing your thoughts like this...
    Why Oracle Cloud
    What is Oracle Cloud

    ReplyDelete
  5. Asia’s premier FinTech certification course in collaboration with S. P. Jain School of Global Management. The best and most comprehensive FinTech course designed to transform your career. Enroll Now!

    ReplyDelete
  6. Hello
    My name is Elisabeth and I would like to ask if there is any Guest or Sponsored post option available on your website
    I would like to post a unique and high quality article with a dofollow link inside
    Please let me know all the guidelines for a perfect article

    Best Regards
    Elisabeth Muller
    elismullermarketing@gmail.com

    ReplyDelete
  7. Nice article. We offer the advanced drug diversion detection software provides you positive accuracy with advanced analytics and actionable insights. Contact us now!

    ReplyDelete
  8. สล็อตแมชชีนได้เงินจริงและเล่นง่าย ผู้เริ่มต้นต้องลองเกมสล็อตแมชชีนจากค่าย PG Slot เล่นง่ายและไม่ซับซ้อน และเป็นเว็บไซต์เดียวที่มีอัตราการจ่ายสูงสุดสำหรับเกมสล็อต ทางเข้าเล่น pg คลิกที่นี้ และยังติดตั้งระบบฝากเงินรวดเร็วและระบบอัตโนมัติภายใน 1 นาทีโดยไม่ต้องรออีกต่อไป

    ReplyDelete