Analytical SQL - SUM without Window Clause

This is my first post in 2022, and with this I am starting a new series on Analytical SQL

In The first example, we are trying to list all records from EMP and DEPT tables with the SUM of SAL column with each row. As you can see here, we have used "sum(sal) over()" without WINDOW ( partition by), which made Oracle to process all rows in table.


select d.deptno, d.dname, empno, ename, 
sum(sal) over() total
from emp e, dept d
where e.deptno = d.deptno;

    DEPTNO DNAME               EMPNO ENAME           TOTAL
---------- -------------- ---------- ---------- ----------
        10 ACCOUNTING           7839 KING          29031.2
        30 SALES                7698 BLAKE         29031.2
        10 ACCOUNTING           7782 CLARK         29031.2
        20 RESEARCH             7566 JONES         29031.2
        20 RESEARCH             7788 SCOTT         29031.2
        20 RESEARCH             7902 FORD          29031.2
        20 RESEARCH             7369 SMITH         29031.2
        30 SALES                7499 ALLEN         29031.2
        30 SALES                7521 WARD          29031.2
        30 SALES                7654 MARTIN        29031.2
        30 SALES                7844 TURNER        29031.2
        20 RESEARCH             7876 ADAMS         29031.2
        30 SALES                7900 JAMES         29031.2
        10 ACCOUNTING           7934 MILLER        29031.2
Above SQL is equivalent to
select d.deptno, d.dname, empno, ename, 
(select sum(sal) from emp) total
from emp e, dept d
where e.deptno = d.deptno;


Related Posts 

11 comments:

  1. Thank you for providing this blog really appreciate the efforts you have taken into curating this article if you want you can check out data science course in bangalore they have a lot to offer with regards to data science in terms of training and live projects.

    ReplyDelete
  2. Great very helpful blog. Thanks For Sharing Such A Wonderful Blog. I will definitely go ahead and take advantage of this. Your Blog Is Very Informative. Again Thanks For Sharing This Blogs With Us. For more learning go through Skillslash.
    For Data Science Course Data Science Course In Bangalore

    ReplyDelete
  3. I truly appreciate the time and work you put into sharing your knowledge. I found this topic to be quite effective and beneficial to me. Thank you very much for sharing. Continue to blog.

    Data Engineering Services 

    Artificial Intelligence Solutions

    Data Analytics Solutions

    Data Modernization Solutions

    ReplyDelete
  4. I propose merely very good along with reputable data, consequently visualize it: Satta king game

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Nice Blog! such a informative things you are sharing ,I really liked your content. If you wanna know about "Skillslash | Training with live industry experience that gets you hired" go to Data Science Course

    ReplyDelete
  7. Fantastic article. I like your writing style. It's inspiring and I am truly grateful for the writing style. Visit my site.
    Black satta king

    ReplyDelete
  8. The popularity of playing Satta king online in India is increasing day by day. Some people play Satta games offline, some people play Satta games using internet. He wants to get rich quick. If you want to play Satta or you want to gain knowledge about satta king, then you must read this article because I will give you complete information about all kinds of entities, advantages or disadvantages related to Satta king.

    ReplyDelete
  9. 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
  10. Your style is very unique in comparison to other people I’ve read stuff from. Thanks for posting when you’ve got the opportunity, Guess I will just bookmark this site.
    UOK BCom 1st Year Exam Result 2021 | UOK BCom Exam Result 2021 | UOK BCom 2nd Year Exam Result 2021.

    ReplyDelete