Window functions: How to partition over nothing?

Question

I am extracting a table, but I would also like the sum of a column.

I can say SUM(column) over (partition by other_column)

to get a new column with a sum over the column for every grouping given by other_column.

But I don't want a grouping! Basically sum(column) is meant to give me a column with a constant row equal to the sum of the entire column with no partitioning.

So how do I partition over nothing?

Answer 1

Exactly like you said; over "nothing". For example:

SQL> select deptno, ename, sal, sum(sal) over () sumsal
  2  from emp;

    DEPTNO ENAME             SAL     SUMSAL
---------- ---------- ---------- ----------
        20 SMITH             920      34145
        30 ALLEN            1600      34145
        30 WARD             1250      34145
        20 JONES            2975      34145
        30 MARTIN           1250      34145
        30 BLAKE            2850      34145
        10 CLARK            2450      34145
        20 SCOTT            3000      34145
        10 KING            10000      34145
        30 TURNER           1500      34145
        20 ADAMS            1100      34145
        30 JAMES             950      34145
        20 FORD             3000      34145
        10 MILLER           1300      34145

14 rows selected.

SQL>