Write SQL to verify cumulative sum

Question

I have a table structure

accountID	amount	total	seq
a1	1	10	0
a1	-10	11	1
a1	2	1	2
a2	20	1	0
a2	1	1	1

The total of the first record is not necessary to be 0

If its not the first record, the current total should be calculated by previous_record.amount + previous_record.total

In the example above, I want to select the last record because the total is wrong. It supposes equal to 21 (1+20).

I have coded a program that retrieve all data and do the verification. But the data size is too big (~1M records). It takes really long time. So I seek a solution that use SQL.

Your question is ambiguous. Do you want the rows where the total is off? Do you want the rows where the calculated totals are off? For instance, if there were a fourth row for the first account, with the values of 3/3 would you want it included? If the values were 3/1 would you want it? (The first is correct based on the data in the previous row; the second is correct based on the data in all previous rows.) — Gordon Linoff
– Gordon Linoff, Commented Mar 30, 2021 at 11:04

Tim Biegeleisen · Accepted Answer · 2021-03-30 09:34:17Z

You may flag any offending record using the LAG() function:

WITH cte AS ( SELECT *, LAG(total) OVER (PARTITION BY accountID ORDER BY seq) AS lag_total, LAG(amount) OVER (PARTITION BY accountID ORDER BY seq) AS lag_amount FROM yourTable ) SELECT accountID, amount, total, seq FROM cte WHERE total <> lag_amount + lag_total;

Collectives™ on Stack Overflow

Write SQL to verify cumulative sum

1 Answer 1

Demo

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related