SQL Standard

# SQL Running Total

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:

window_function ( COLUMN )
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:

SELECT product_name, product_category, quantity, SUM(quantity) OVER (ORDER BY product_name) AS cum_total
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.:

SELECT COLUMN(s)
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! 