In SQL, a running total refers to the sum of all the values in the previous rows in a specific column.
For example, consider the table shown below:
Note that the cum_total column holds the total quantity at that current row. For example, in the first row, the total was 500; in the second, the sum is equal to the previous_row + current row.
The cumulative total is compounded after each column by taking the sum of all the previous rows and adding it to the current row’s value.
SQL Calculate Running Total
The best way to calculate the running total is to use window functions. A window function allows you to perform a specific operation on rows and return a single aggregate value for each row.
The syntax below illustrates how to use a window function to calculate the running total:
OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )
An example is to use the sum() function.
Suppose we have a table as shown below:
To calculate the running total, we can use a windows function as shown below:
FROM products;
You must use the over clause with a window function when determining a running total.
Suppose you have the data organized into various groups. For example, the quantity of each product_category is shown individually?
We can add the partition by clause as shown in the syntax below.:
aggregate_function(column_name)
OVER (PARTITION BY category ORDER BY )
AS column_name
FROM TABLE_NAME;
The query should partition the data into various groups and calculate their running total.
Conclusion
This article covered the running total and how you can calculate it in SQL using a window function.
Thanks for reading, and stay tuned for more!
