SUM

Running totals

SQLite in your browser
Lesson concept What you'll do
1 / 4

Add up as you go down the rows.

SUM(...) OVER (ORDER BY ...) gives each row the total so far.

The database
11 rows
book_idtitleauthorgenreyearpages
1Pride and PrejudiceJane AustenRomance1813432
2The Fault in Our StarsJohn GreenRomance2012313
3Angels & DemonsDan BrownThriller2000616
4MockingjaySuzanne CollinsScience Fiction2010390
5CarrieStephen KingHorror1974200
6Charlotte's WebE.B. WhiteChildren1952184
71984George OrwellScience Fiction1949328
8The Fellowship of the RingJ.R.R. TolkienFantasy1954423
9The Time MachineH.G. WellsScience Fiction1895118
10DuneFrank HerbertScience Fiction1965412
11DraculaBram StokerHorror1897418
Click a name to type it
Your query
Hint: click the green edit button on the board, or type it yourself
Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How SUM, OVER works

SUM(pages) OVER (ORDER BY year) adds up the pages of every row so far, oldest first.

titleyearpagespages_so_far
Frankenstein1818280280first book: just its pages
Dracula1897418698280 + 418
Brave New World19323111009698 + 311
Dune196541214211009 + 412
Carrie197420016211421 + 200
The Hunger Games200837419951621 + 374

query

SELECT title, year, pages,
  SUM(pages) OVER (ORDER BY year) AS pages_so_far
FROM books;

Every row stays: GROUP BY would squash each genre into one row instead.

Recap: window functions side by side

  • How do I make a running total?

    SUM(pages) OVER (ORDER BY year)

    Each row adds up its own value and every row before it, in the ORDER BY order. Every row stays in the result.

  • What if I leave out ORDER BY?

    SUM(pages) OVER () puts the grand total on every row. Handy for percentages: pages * 100.0 / SUM(pages) OVER ().

  • Can it restart for each group?

    Yes: OVER (PARTITION BY genre ORDER BY year) keeps a separate running total per genre.

  • What about ties in the ORDER BY?

    Rows that tie are added together, so they show the same total. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW inside OVER to add them one at a time.

  • How is this like Excel?

    The classic running total =SUM($F$2:F2) filled down, with the first cell locked and the last one moving.

Know someone fighting with a spreadsheet? Send them this page.