Skip to content
Advertisement

Null Calculation With SQL Select Statement

I have Following Table

If I pass Following Sql Statement:

Select Input,Output,Balance=sum(Input – Output) from Stock group by input,output

So The Output is:

If I wants The Output Like Below:

Than What is SQL Transact Statement?

My Table Structure is as Below:

Note:- I have applied here Group By function on input and Output columns which doesn’t make diference as there is transID columns which is autoincrement hence it should be display all the rows of the table.

Advertisement

Answer

I think this may work for you. First, here is how I defined my test table and test data:

And here is the select I used to produce the results you specified in your question. The query produces a total for all transactions in transaction ID order for a given day. If you want to calculate the total for multiple days, then comment-out the AND b.[date] = a.[date] line.

This query gives:

As a footnote, since the running total value will always be the same for each transaction, I would recommend adding a balance column to your table and calculating the value of the balance column upon insertion of the transaction row. In that way, you would only have to look at the balance of the last transaction to determine what the balance for the transaction being inserted should be.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement