MariaDB running total up to N and rows NOT included in its calculation

Question

I have a table which amongst other columns has amt and created(timestamp).

1. I'm trying to calculate the running total of amt up to N
2. Get all the rows not included in the calculation leading to the sum up to N

I'm doing this in code but was wondering if there was a way to get these with SQL and ideally in one query.

Looking around and it's easy to find examples of calculating the running total like https://stackoverflow.com/a/1290936/400048 but less so to find running total up N and then only actually return rows not involved in calculating N.

Answer 1

You can use the window version of the SUM aggregate function to get the running total for each row.

CREATE TABLE TEST (ID BIGINT PRIMARY KEY, AMT INT, CREATED TIMESTAMP);

INSERT INTO TEST VALUES
(1, 1, TIMESTAMP '2000-01-01 00:00:00'),
(2, 2, TIMESTAMP '2000-01-02 00:00:00'),
(3, 1, TIMESTAMP '2000-01-03 00:00:00'),
(4, 3, TIMESTAMP '2000-01-04 00:00:00'),
(5, 5, TIMESTAMP '2000-01-05 00:00:00'),
(6, 1, TIMESTAMP '2000-01-07 00:00:00');

SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST ORDER BY CREATED;

> ID AMT RT CREATED
> -- --- -- -------------------
> 1  1   1  2000-01-01 00:00:00
> 2  2   3  2000-01-02 00:00:00
> 3  1   4  2000-01-03 00:00:00
> 4  3   7  2000-01-04 00:00:00
> 5  5   12 2000-01-05 00:00:00
> 6  1   13 2000-01-07 00:00:00

Then you can use a non-standard QUALIFY clause in H2 or a subquery (in both MariaDB and H2) to filter out rows below the limit.

If N is a running total limit and by “rows not included in the calculation” you mean rows above the limit, the queries will look like these:

-- Simple non-standard query for H2
SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST
    QUALIFY RT > 10 ORDER BY CREATED;
-- Equivalent standard query with subquery for MariaDB, H2, and many others
SELECT * FROM (
    SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST
) T WHERE RT > 10 ORDER BY CREATED;

> ID AMT RT CREATED
> -- --- -- -------------------
> 5  5   12 2000-01-05 00:00:00
> 6  1   13 2000-01-07 00:00:00

RT - AMT in the first row here is a running total of all previous rows. You can select it separately, if you wish:

-- Non-standard query for H2
SELECT SUM(AMT) OVER (ORDER BY CREATED) RT FROM TEST
    QUALIFY RT < 10 ORDER BY CREATED DESC FETCH FIRST ROW ONLY;
-- Non-standard query for MariaDB or H2
SELECT RT FROM (
    SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST
) T WHERE RT < 10 ORDER BY CREATED DESC LIMIT 1;
-- Standard query for H2 and others (but not for MariaDB)
SELECT RT FROM (
    SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST
) T WHERE RT < 10 ORDER BY CREATED DESC FETCH FIRST ROW ONLY;

> RT
> --
> 7

If you meant something else, the QUALIFY or WHERE criteria will be different.