Window functions: How to partition over nothing?

user_14872617 0
Viewed 1606

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?

window-functions oracle
2 Answers

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>
user_90979060

Let's see the table orders created as follows:

Schema (MySQL v8.0)

CREATE TABLE orders (
  `trade_date` DATETIME,
  `ticker` VARCHAR(4),
  `trans_type` VARCHAR(4),
  `quantity` INTEGER
);

INSERT INTO orders
  (`trade_date`, `ticker`, `trans_type`, `quantity`)
VALUES
  ('2020-12-10', 'FB', 'BUY', '100'),
  ('2020-12-28', 'FB', 'BUY', '50'),
  ('2020-12-29', 'FB', 'SELL', '80'),
  ('2020-12-30', 'FB', 'SELL', '30'),
  ('2020-12-31', 'FB', 'BUY', '40'),
  ('2020-11-16', 'AAPL', 'BUY', '30'),
  ('2020-11-17', 'AAPL', 'SELL', '70'),
  ('2020-11-20', 'AAPL', 'BUY', '50'),
  ('2020-11-24', 'AAPL', 'BUY', '40');

And we want to sum over the quantity by the trans_type:

Query #1

SELECT
    trade_date,
    ticker,
    trans_type,
    quantity,
    SUM(CASE WHEN trans_type='SELL' THEN -quantity ELSE quantity END) OVER () AS net_quantity
FROM
    orders;

We will get this table:

trade_date ticker trans_type quantity net_quantity
2020-12-10 00:00:00 FB BUY 100 130
2020-12-28 00:00:00 FB BUY 50 130
2020-12-29 00:00:00 FB SELL 80 130
2020-12-30 00:00:00 FB SELL 30 130
2020-12-31 00:00:00 FB BUY 40 130
2020-11-16 00:00:00 AAPL BUY 30 130
2020-11-17 00:00:00 AAPL SELL 70 130
2020-11-20 00:00:00 AAPL BUY 50 130
2020-11-24 00:00:00 AAPL BUY 40 130

View on DB Fiddle

This article would be helpful for you to learn window functions: An Intro to SQL Window Functions.

Reference:
mysql window function with case

user_35529750
Related