I'm trying to get one column of numbers to subtract from another column and to keep a running total. Column A is a dollar amount from an invoice. Column B starts out with an opening amount ($10,000) and I want to have Column A to subtract from that.
COLUMN A COLUMN B $0.00 $10,000 $1,000 $9,000 $500 $8,500 $2,000 $6,500
This is one of three questions this week, to which, I'm afraid, the answer is no.[The others were Entity-Attribute-Value constraints and SQL to update the header records?]
No, this is not possible, not with the information you've given. You've overlooked the need to have a sequencing column. But once we resolve this, the rest is easy.
Somewhere in every basic SQL tutorial should be the idea that there is no sequence in database tables. The rows could be placed higgledy-piggledy anywhere on the disk drive, and not in any particular sequence, but in "disorderly and utterly irregular fashion."
Even if you use a clustered index (search this term for more information; then ask yourself what column would you declare the clustered index on), you are not actually guaranteed that the database will actually store rows in clustered index sequence. The database might have an occasional hiccough and need to stick the row elsewhere. Sequence in SQL is guaranteed only when you use the ORDER BY clause, or, alternatively, when you use a comparison operator such as "less than" on the distinct values in a sequencing column. Incrementing numbers make good sequences, and gaps are fine; the numbers do not need to be consecutive because allowing for gaps is easier than assuring that there are none. Timestamps are also okay, unless you get multiple invoices per millisecond.
Once you have settled on a sequencing column, the rest is easy. Do a LEFT OUTER JOIN from each row to the previous one. This is a self join. It has to be a LEFT OUTER JOIN because you want to include the first row returned in sequence, and the first row doesn't have a previous row. How do you know which one is the previous one? It's the one with the highest sequence value that is less than the sequence value of this one. So the join condition would be something like:
from invoices as this left outer join invoices as prev on prev.seq = ( select max(seq) from invoices where seq < this.seq )
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading