Understanding Running Totals in DB2

In the context of database management systems like DB2, running totals are a calculation that sums up all values for a specific period or group. In this article, we’ll explore how to calculate month-to-date (MTD) sales using running totals in DB2.

Background on SQL and Window Functions

SQL is a programming language designed for managing relational databases. To perform calculations like MTD sales, you need to use window functions, which are a set of functions that allow you to perform operations across rows that share some common characteristic. In this case, we’re interested in using the SUM() function with an over-clause.

The SUM() function calculates the sum of all values within a specified group. When used with a window clause, it can calculate the running total by aggregating data across all rows and returning one value per row.

The Problem with Static Totals

In the provided example, you’ve already attempted to use static totals using an inner join with the result of a subquery that calculates the sum of sales for each month. However, this approach has limitations when trying to compare MTD sales across different years or weeks.

To get around these limitations, we need to use window functions that can handle running totals and partitions. This will allow us to calculate the total sales up to the current week for each month.

Using Window Functions

The key to calculating MTD sales using running totals is to use a window function like SUM() with an over-clause at the original row level, rather than inside an aggregate query.

SELECT
  s.Year,
  s.Month,
  s.Week,
  s.Sales,
  SUM(s.sales) over (PARTITION BY s.Year, s.Month ORDER BY s.Week) as running_total
FROM sales s;

Partitioning and Ordering

When using the SUM() function with a window clause, it’s essential to specify the partitioning criteria (i.e., how to group rows) and ordering criteria.

In this example, we’re partitioning by year and month. This means that for each unique combination of these two columns, we’ll calculate the running total. The ORDER BY s.Week part specifies that within each partition, the rows should be ordered by week number.

Why This Works

Using the SUM() function with a window clause allows us to:

• Calculate the sum of sales for all rows in each group.
• Return one value per row, which represents the running total up to that point.
• Partition the data by year and month, allowing us to compare MTD sales across different periods.

Example Use Case

Here’s an example of how you might use this query:

Suppose we have a table called sales with columns for year, month, week, and sales. We want to calculate the running total of sales for each month up to the current week.

-- Create a sample dataset
CREATE TABLE sales (
  Year INT,
  Month VARCHAR(3),
  Week INT,
  Sales DECIMAL(10,2)
);

INSERT INTO sales (Year, Month, Week, Sales) VALUES
  (2018, 'Jul', 1, 2000.00),
  (2018, 'Jul', 2, 1500.00),
  (2018, 'Jul', 3, 3000.00),
  (2019, 'Jul', 1, 2500.00),
  (2019, 'Jul', 2, 3200.00);

Now, let’s run the query:

SELECT
  s.Year,
  s.Month,
  s.Week,
  s.Sales,
  SUM(s.sales) over (PARTITION BY s.Year, s.Month ORDER BY s.Week) as running_total
FROM sales s;

This will return a result set with the original data and a new column called running_total, which contains the sum of sales up to the current week for each month.

DBFiddle Demo

Here’s a link to a DBFiddle demo that you can use to test this query:

DBFiddle Demo

This demo shows how the SUM() function with a window clause can be used to calculate running totals in DB2. By partitioning by year and month, we can compare MTD sales across different periods.

In conclusion, using window functions like SUM() with an over-clause is an effective way to calculate running totals in DB2. By specifying the partitioning criteria (year and month) and ordering criteria (week), we can get accurate results for comparing MTD sales across different years or weeks.

Last modified on 2025-04-22