SQL Resources
SQL
Running Totals

Running Totals

Running Totals or Cumulative Sums are a powerful way to see not just a trend of data, but also the cumulative results.

For example, if you wanted to monitor your monthly sales, but also make sure you're on track to achieve your annual goal, a running total will sort you out:

In the chart above, the running total line in blue shows how close we are to the annual goal of $300M.

Step-by-Step

Running Totals rely on the use of Window Functions, which you can read more about here.  

To find a running total we'll use the SUM window function:

SUM( <expr1> ) OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ] )

Where:

  • expr1 This is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).
  • expr2 This is the optional expression to partition by.
  • expr3 This is the optional expression to order by within each partition. (This does not control the order of the entire query output.)
  • Or, in layman's terms:

    SUM(column) OVER (PARTITION BY col_to_group_by, ORDER BY column_to_order_by)

    In the following example we'll take the running total of hours of Netflix I watched in a given week:

    SELECT 
      DATE_TRUNC('WEEK',START_TIME) WEEK,
      DATE_PART('WEEKDAY',START_TIME) DAY, 
      SUM(DURATION)/(60*60) DAILY_HOURS_WATCHED 
    FROM 
      PUBLIC.NETFLIX 
    WHERE 
      WEEK = '2018-11-26'
    GROUP BY WEEK,DAY
    ORDER BY WEEK,DAY
    WEEKDAYDAILY_HOURS_WATCHED
    2018-11-26T00:00:00.000Z04.412777778
    2018-11-26T00:00:00.000Z11.467222222
    2018-11-26T00:00:00.000Z20.6561111111
    2018-11-26T00:00:00.000Z40.5063888889
    2018-11-26T00:00:00.000Z50.5261111111
    2018-11-26T00:00:00.000Z60.8688888889

    Now if I want to find the running total of hours watched that week, I can do the following:

    SUM(DAILY_HOURS_WATCHED) OVER (ORDER BY DAY ASC) RUNNING_TOTAL
    SELECT DAY, DAILY_HOURS_WATCHED, SUM(DAILY_HOURS_WATCHED) OVER (ORDER BY DAY ASC) RUNNING_TOTAL FROM ONE_WEEK
    DAYDAILY_HOURS_WATCHEDRUNNING_TOTAL
    04.4127777784.412777778
    11.4672222225.88
    20.65611111116.536111111
    40.50638888897.0425
    50.52611111117.568611111
    60.86888888898.4375

    We can see our new RUNNING_TOTAL column increase each day.

    This example had no partition, but if we wanted to compare my viewing habits across two different weeks, we would need to PARTITION BY week:

    SELECT 
      DATE_TRUNC('WEEK',START_TIME) WEEK,
      DATE_PART('WEEKDAY',START_TIME) DAY, 
      SUM(DURATION)/(60*60) DAILY_HOURS_WATCHED 
    FROM 
      PUBLIC.NETFLIX 
    WHERE 
      WEEK IN ('2018-11-19','2018-11-26')
    GROUP BY WEEK,DAY
    ORDER BY WEEK,DAY
    WEEKDAYDAILY_HOURS_WATCHED
    2018-11-19T00:00:00.000Z01.326666667
    2018-11-19T00:00:00.000Z10.4775
    2018-11-19T00:00:00.000Z20.8708333333
    2018-11-19T00:00:00.000Z60.3
    2018-11-26T00:00:00.000Z04.412777778
    2018-11-26T00:00:00.000Z11.467222222
    2018-11-26T00:00:00.000Z20.6561111111
    2018-11-26T00:00:00.000Z40.5063888889
    2018-11-26T00:00:00.000Z50.5261111111
    2018-11-26T00:00:00.000Z60.8688888889

    And now to find the running total across the two weeks:

    SUM(DAILY_HOURS_WATCHED) OVER(PARTITION BY WEEK ORDER BY DAY ASC) WEEKLY_RUNNING_TOTAL
    SELECT 
      WEEK, DAY, DAILY_HOURS_WATCHED, SUM(DAILY_HOURS_WATCHED) OVER(PARTITION BY WEEK ORDER BY DAY ASC) WEEKLY_RUNNING_TOTAL
    FROM 
      TWO_WEEKS
    ORDER BY WEEK,DAY
    WEEKDAYDAILY_HOURS_WATCHEDWEEKLY_RUNNING_TOTAL
    2018-11-19T00:00:00.000Z01.3266666671.326666667
    2018-11-19T00:00:00.000Z10.47751.804166667
    2018-11-19T00:00:00.000Z20.87083333332.675
    2018-11-19T00:00:00.000Z60.32.975
    2018-11-26T00:00:00.000Z04.4127777784.412777778
    2018-11-26T00:00:00.000Z11.4672222225.88
    2018-11-26T00:00:00.000Z20.65611111116.536111111
    2018-11-26T00:00:00.000Z40.50638888897.0425
    2018-11-26T00:00:00.000Z50.52611111117.568611111
    2018-11-26T00:00:00.000Z60.86888888898.4375

    Now we can see the RUNNING_TOTAL restart for the 2nd week.

    How We Built This

    This page was built using Count. It combines the best features of a SQL IDE, Data Visualization Tool, and Computational Notebooks. In the Count notebook, each cell acts like a CTE, meaning you can reference any other cell in your queries.

    This makes not only for far more readable reports (like this one) but also a much faster and more powerful way to do your analysis, essentially turning your analysis into a connected graph of data frames rather than one-off convoluted queries and CSV files. And with a built-in visualization framework, you won't have to export your data to make your charts. Go from raw data to interactive report in one document.

    Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.