12 May 20 · npack · #Snowflake ·   Bookmark   ×

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:

Student IdSubject NameMarksRunning Total
1Maths8585
1English90175
1Science75250
1Arts50300

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
StudentIdSubjectName
Marks
RunningTotal
RunningAvg
1Maths909090
1English6015075
1Science5020067
2Maths858585
2Arts6515075

You can use the above query syntax in:

  • Google BigQuery
  • Snowflake Cloud Data Warehouse
  • Teradata

npack

posted on 12 May 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds