Recursive AI

Calculate a SQLite running total in C#

OneLite is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

A running total adds each row to the rows before it, without collapsing the result into a single sum.

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneLite;
 
await using var db =
    await Database.OpenAsync(":memory:");
var totals =
    await db.QueryAsync<long>($"""
    WITH sales(id, day, cents) AS (
      VALUES(1,'2026-01-01',100),
            (2,'2026-01-01',200),
            (3,'2026-01-02',50)
    )
    SELECT sum(cents) OVER (
      ORDER BY day, id
      ROWS BETWEEN UNBOUNDED PRECEDING
               AND CURRENT ROW)
    FROM sales ORDER BY day, id
    """);
foreach (var total in totals)
    Console.WriteLine(total);
Text
100
300
350

The input is three sales in integer cents, including two on the same date. The snippet at the top prints one cumulative amount per sale, ordered by day and unique ID. If the SQL fails, OneLite throws OneLiteException carrying the SQL error code and message.

The query methods require interpolated strings: values inserted with {value} become bound SQL parameters.

Unzip the example, then dotnet run.

Variations

Compare the default peer frame: without a ROWS frame, rows with the same day share one total. Replace the full SQL inside QueryAsync<long> with:

SQL
WITH sales(id, day, cents) AS (
  VALUES(1,'2026-01-01',100),
        (2,'2026-01-01',200),
        (3,'2026-01-02',50)
)
SELECT sum(cents) OVER (ORDER BY day)
FROM sales ORDER BY day, id
Text
300
300
350

Both sales on the first day now have the same cumulative total.

The short answer

Use sum(cents) OVER (ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) and repeat the same ordering on the outer SELECT. In C#, OneLite’s QueryAsync<long> reads the numeric result column into a list. The snippet at the top includes its input, so it runs without an existing database.

The important choices are the frame and the tie-breaker. ROWS means individual rows; UNBOUNDED PRECEDING starts at the beginning; CURRENT ROW includes the current sale. Adding a unique ID after the date determines which same-day sale comes first. Those choices give a per-transaction cumulative total rather than a per-date total repeated on every transaction.

How do you calculate a running total with a window function?

An aggregate normally reduces rows to a result for each group. An aggregate window function keeps a result for every input row and calculates over a frame of related rows. OneLite documents OVER, PARTITION BY, ORDER BY and ROWS/RANGE/GROUPS frames in its window-function support.

For a running total, the frame grows from the beginning to the current row. The example’s first sale contributes 100 cents; the second adds 200; the third adds 50. The resulting values are 100, 300 and 350. They are cents, not formatted dollar amounts.

Use a business-defined order. A date alone may not establish the sequence of transactions within a day. Here the unique ID is the tie-breaker, not an assumption about insertion order. If your ledger has an explicit sequence number, use that instead. The outer ORDER BY controls presentation; the one inside OVER controls the calculation.

Why do rows with the same date have the same running total?

The SQLite window-frame documentation defines the default as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS. With ORDER BY day, all rows on the same day are peers and enter the frame together. The variation demonstrates that behavior with the same input: the first two output values become 300 and 300.

That result is useful if you want the cumulative balance at the end of each date repeated for every transaction on that date. It is not the row-by-row balance. An explicit ROWS frame plus a unique ordering key communicates the latter intention and avoids leaving tie order unspecified. If you want only one result per date, first aggregate the source into daily amounts, then calculate over those daily rows.

Does SQLite have window functions?

Yes; the SQLite documentation describes aggregate windows and built-ins such as ranking and offset functions. In this recipe, OneLite executes the SQLite-style SQL in managed .NET code. Its pinned engine documentation lists every aggregate as available as a window function, including sum.

For independent balances, add an appropriate PARTITION BY key inside the window so the accumulation starts again for each account or category. Keep the chronological ordering and explicit frame within each partition. This is distinct from GROUP BY: partitioning preserves the transaction rows, while grouping changes the level of detail returned.

All onelite recipes