# SQL Running Total and Window Function Query Generator for ERP Tables

> Source: https://netray.co/tools/sql-running-total-window-function-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your table and column names to get running total, previous-row change and top-N per group queries using window functions.

## Category

developer

## Audience

- ERP analyst
- Report developer
- Finance analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Table or view

**Key:** table

**Type:** text

**Default:** dbo.inventory_transactions

#### Group column (partition)

**Key:** group_col

**Type:** text

**Default:** item_code

#### Order column

**Key:** order_col

**Type:** text

**Default:** transaction_date

#### Tie-break column (unique)

**Key:** tiebreak_col

**Type:** text

**Default:** transaction_id

#### Amount or quantity column

**Key:** amount_col

**Type:** text

**Default:** quantity_signed

#### Extra filter (without WHERE)

**Key:** filter

**Type:** text

**Default:** warehouse = 'MAIN'

#### Top N per group

**Key:** top_n

**Type:** number

**Default:** 3

**Blocks:**

#### Running total per group

**Body:** SELECT
    {{group_col}},
    {{order_col}},
    {{tiebreak_col}},
    {{amount_col}},
    SUM({{amount_col}}) OVER (
        PARTITION BY {{group_col}}
        ORDER BY {{order_col}}, {{tiebreak_col}}
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM {{table}}
WHERE {{filter}}
ORDER BY {{group_col}}, {{order_col}}, {{tiebreak_col}};

#### Change from previous row

**Body:** SELECT
    {{group_col}},
    {{order_col}},
    {{amount_col}},
    LAG({{amount_col}}) OVER (
        PARTITION BY {{group_col}}
        ORDER BY {{order_col}}, {{tiebreak_col}}
    ) AS previous_value,
    {{amount_col}} - LAG({{amount_col}}, 1, 0) OVER (
        PARTITION BY {{group_col}}
        ORDER BY {{order_col}}, {{tiebreak_col}}
    ) AS change_from_previous
FROM {{table}}
WHERE {{filter}};

#### Latest N rows per group

**Body:** WITH ranked AS (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY {{group_col}}
            ORDER BY {{order_col}} DESC, {{tiebreak_col}} DESC
        ) AS rn
    FROM {{table}} AS t
    WHERE {{filter}}
)
SELECT *
FROM ranked
WHERE rn <= {{top_n}}
ORDER BY {{group_col}}, rn;

#### Share of group total

**Body:** SELECT
    {{group_col}},
    {{order_col}},
    {{amount_col}},
    SUM({{amount_col}}) OVER (PARTITION BY {{group_col}}) AS group_total,
    CASE WHEN SUM({{amount_col}}) OVER (PARTITION BY {{group_col}}) = 0
         THEN 0
         ELSE 100.0 * {{amount_col}} / SUM({{amount_col}}) OVER (PARTITION BY {{group_col}})
    END AS pct_of_group
FROM {{table}}
WHERE {{filter}};

**Notes:** Written in ANSI-style SQL that runs on SQL Server 2012 and later, Oracle, PostgreSQL and recent MySQL. Table and column names are placeholders for your ERP, so check them in your data dictionary. Test on a small date range first.

## Content

**Intro:** Running balances, change from the previous transaction and latest-N-per-item are everyday ERP report needs. This generator writes the window function version so you stop using slow self-joins.

**Sections:**

#### How to use the generator

**Body:** Enter the table or view name, the column that groups rows (item, account, customer), the column that orders them and a unique tie-breaker such as a transaction id. Set the amount column that should accumulate and an optional filter. Copy the query you need into your SQL tool. Replace the placeholders with real names from your ERP, and run first with a narrow filter so you can check the first few rows against the ERP screen.

#### Why the frame clause and tie-break matter

**Body:** When ORDER BY is given without a frame, many engines default to RANGE, which treats rows with the same order value as one group. Two transactions posted at the same date would then show the same running total. Writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and adding a unique tie-break column makes the result deterministic, and ROWS is also generally faster. Without a tie-break, results can change between runs.

#### Reading the output

**Body:** For an inventory ledger the running total gives the balance after each movement, provided the quantity is signed (receipts positive, issues negative) and the query starts at the first transaction. If you filter by date, the running total starts at zero in the window, so add an opening balance. The latest-N query is useful for the last three receipt costs of an item, and the share of group total shows concentration, such as one customer holding most of the open balance.

#### Common mistakes

**Body:** People often filter on a window result in the WHERE clause, which is not allowed because windows run after filtering. Wrap the query in a common table expression, as the latest-N block does. Others forget that NULL amounts propagate in LAG and SUM, apply the filter after the window so balances are wrong, or run it on unindexed tables with millions of rows. An index on the group and order columns helps greatly.

**Cta:** If you want these report queries built against your actual ERP schema and scheduled to a share, we can build them with you.

**Faq:**

#### Does this work on older SQL Server versions?

**Answer:** Running totals with an ordered window frame need SQL Server 2012 or later. On SQL Server 2008 you would use a correlated subquery or a cursor, which is much slower on large ledgers.

#### Why is my running total wrong on same-day rows?

**Answer:** Without a unique tie-break in the ORDER BY, rows with equal order values are arbitrary, and a default RANGE frame groups them. Add a unique column and use the ROWS frame as in the generated query.

#### Can I filter on the running total?

**Answer:** Not directly in the same SELECT. Wrap the query in a CTE or subquery and filter the outer query, for example to find the first date the balance went negative.

#### How do I start the balance from an opening figure?

**Answer:** Calculate the balance before your start date separately and add it to the running total, or run the window over the full history and filter the outer query by date.

## Search terms

sql running total, sql window function example, running balance sql erp, sql lag previous row, sql row_number top n per group, inventory running balance query, sum over partition by order by, sql cumulative sum generator

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json