Using aggregate and window function within a sub-query

Question

I have a situation where I have a query with a sub-query that uses SUM() OVER. While the sub-query works fine on its own, when used in-context of the outer query, the aggregation isn't applied.

I've made this test case to approximate what I'm trying to do:

CREATE TEMPORARY TABLE TestNumbers
  (id INTEGER PRIMARY KEY, number INTEGER);
INSERT INTO TestNumbers (number)
  VALUES (10), (15), (20), (25), (30);

SELECT SUM(number) OVER (ORDER BY id) FROM TestNumbers;

This returns the expected result... a rolling sum of all values. 10, 25, 45, 70, 100.

However, if I use this as a sub-query...

SELECT (
  SELECT SUM(number) OVER (ORDER BY id)
) FROM TestNumbers;

I get a result with the aggregation not applied. 10, 15, 20, 25, 30.

How can I use this aggregation with the OVER in a sub-query?

Answer 1

I am surprised that this even works:

SELECT (SELECT SUM(number) OVER (ORDER BY id))
FROM TestNumbers;

I mean, it does. Basically, the subquery is correlated subquery, so it runs for each row in the outer query. That is, there is just a single value for each run -- the correlated value from the outer query. That is just going to return the number.

It is not clear what you really want to do.

When I first read the query, I thought it was:

SELECT *
FROM (SELECT SUM(number) OVER (ORDER BY id))
      FROM TestNumbers
     ) tn

This is fine and works with the accumulated value.

Moving the FROM to the subquery:

SELECT (SELECT SUM(number) OVER (ORDER BY id))
        FROM TestNumbers
       )

Breaks the query because the subquery returns more than one row.