Free SQL Help for Devs, DBAs, and the Curious

In this blog post, we will explore how to calculate running totals using window functions across multiple SQL technologies - SQL Server 2016 and 2017, MySQL 5.7, DB2 11.1, and Azure SQL. Window functions are a powerful tool in SQL that allow you to perform calculations across a set of table rows that are related to the current row.

A running total, or a cumulative sum, is a sequence of partial sums of a given sequence. For instance, for a sequence of numbers 1, 2, 3, 4, 5, a running total is 1, 3, 6, 10, 15. In SQL, you can calculate running totals using a window function called SUM().

In SQL Server (both 2016 and 2017), the syntax for calculating a running total using the SUM() window function is as follows:

SELECT 
    column1, 
    column2, 
    SUM(column3) OVER (ORDER BY column1) AS RunningTotal
FROM 
    table_name;

This SQL Server example calculates a running total of column3, ordered by column1. The OVER clause defines a window or set of rows within the query result set. The ORDER BY clause inside the OVER clause orders the rows in each partition.

In MySQL 5.7, the syntax is slightly different because it does not support window functions like SQL Server. Instead, you can use a variable to calculate a running total:

SET @running_total := 0;
SELECT 
    column1, 
    column2, 
    @running_total := @running_total + column3 AS RunningTotal
FROM 
    table_name
ORDER BY 
    column1;

In DB2 11.1, the syntax for calculating a running total is similar to SQL Server. The only difference is that DB2 uses the keyword ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:

SELECT 
    column1, 
    column2, 
    SUM(column3) OVER (ORDER BY column1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM 
    table_name;

Lastly, Azure SQL supports the same syntax as SQL Server for calculating a running total. It's important to note that the performance of window functions can be impacted by the size of the window. Therefore, it's recommended to use window functions on smaller datasets or implement appropriate indexing strategies to improve performance.