In mysql, What is the difference between sum(a) and sum(a) over()?

Question

I thought that both of them are used to add all the numbers up. But my code could be accepted only by using sum(a) over().

 select avg(1.0*Number) median
 from (
 select *, sum(Frequency) over(order by Number) pos,
(sum(Frequency) over())/2.0 mid
from Numbers
) A
where mid between pos-Frequency and pos

Lerner Zhang · Answer 1 · 2021-01-23T03:44:50.480

From this document we can learn that:

The SQL SUM function is used to return the sum of an expression in a SELECT statement.

The sum returns one value over multiple rows and hence we should be clear if we want to put that scalar in all those rows or one row. If we want to put that in all those rows we can utilize window functions(partition) otherwise we can use group by(aggregation).

We create a table 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');

Let's do it using group by first.

Query #1

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

ticker	net_quantity
FB	80
AAPL	50

View on DB Fiddle

We can see that for each ticker the sum is collapsed into one value, and we cannot add other fields in the select clause.

Let's see the partition method:

Query #2

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

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

View on DB Fiddle

All two sums are appended to each row. You can refer to this answer.

In mysql, What is the difference between sum(a) and sum(a) over()?

1 Answers1