Calculating Running Total with OVER Clause in SQL - Bigquery / Snowflake / Teradata

You often come across scenarios where you have to calculate a running total of a quantity. A running total refers to the sum of values in all cells of a column that are previous to the current row

Example:

You can calculate the running total like below:

SELECT StudentId, SubjectName, Marks,
SUM(Marks) OVER (ORDER BY Id) AS RunningTotal
FROM Students

You can extend this to include AVG as well:

SELECT StudentId, SubjectName, Marks,
SUM(Marks) OVER (ORDER BY Id) AS RunningTotal,
AVG(Marks) OVER (ORDER BY Id) AS RunningAvg
FROM Students

Calculating Running Totals by Partitioning on Column Values:

You can also calculate a running total by partitioning data by the values in a particular column. For instance, you can calculate a running total of the students’ age, partitioned by gender. To do this, you have to use a PARTITION BY statement along with the OVER clause

Instead of calculating running totals for the entire class, it makes sense to do running totals per student, like in the below example:

SELECT StudentId, SubjectName, Marks,
SUM(Marks) OVER (PARTITION BY StudentId ORDER BY Id) AS RunningTotal,
AVG(Marks) OVER (PARTITION BY StudentId ORDER BY Id) AS RunningAvg
FROM Students

You can use the above query syntax in:

• Google BigQuery
• Snowflake Cloud Data Warehouse
• Teradata

nVector

posted on 12 May 20