Sql - Running totals and frames
Code Life
By Pierre Marsaa
Running totals
You have seen ORDER BY inside a window decide a numbering. Put it inside an aggregate's window and something new happens: the aggregate stops summarising the whole window, and each row gets the total so far. Badges earned in the league, in date order:
↗️
Loading editor...
↶
↷
Row by row, badges_so_far climbs from 1 to 10 — ORDER BY inside the window means "accumulate in this order".
Partition it and every trainer gets a personal counter:
↗️
Loading editor...
↶
↷
Alex counts to 3, Morgan to 5, Taylor to 2. The same works with SUM — the cumulative strength of the league, weakest Codémon first:
↗️
Loading editor...
↶
↷
Running = ORDER BY in the window
Without ORDER BY, SUM(level) OVER () is the grand total on every row: 92. With ORDER BY level, it becomes a running total that ends at 92.
Verify