Null Calculation With SQL Select Statement

Question

I have Following Table

Input           Output
---------------------------------
12.22           
                2.22

If I pass Following Sql Statement:

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

So The Output is:

Input            Output       Balance
--------------------------------------
12.22            NULL         NULL
NULL             2.22         NULL

If I wants The Output Like Below:

Input            Output       Balance
--------------------------------------
12.22                         12.22
                 2.22         10.00

Than What is SQL Transact Statement?

My Table Structure is as Below:

companyID   int
transID     int    -- Primary Key (Auto Increment)
Date        datetime
particulars varchar
input       decimal
output      decimal

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.

Answer 1

Select Input,
       Output,
       @runbal:=@runbal+SUM(COALESCE(Input, 0) - COALESCE(Output, 0)) AS Balance
from (select @runbal:=0) rt,Stock 
group by Input,Output

Answer 2

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

declare @stock table (
  transId int not null identity(1,1), 
  [date] date not null, -- use datetime if not using SQL Server 2008
  input decimal(7,2) null, 
  [output] decimal(7,2) null
);

insert into @stock values
('1/23/2011', 12.22, null),
('1/23/2011', null, 2.22),
('1/24/2011', 16, null),
('1/24/2011', 3.76, null),
('1/24/2011', null, 5.50);

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.

select 
  [date],
  input = isnull(input,0), 
  [output] = isnull([output],0),
  balance = (isnull(input,0)-isnull([output],0)) +
        isnull((
          select 
            sum((isnull(input,0)-isnull([output],0)))
          from @stock b
          where b.transId < a.transId
          and b.[date] = a.[date] -- comment this for totals over multiple dates
        ),0)
from @stock a
order by transId;

This query gives:

date        input   output  balance
2011-01-23  12.22   0.00    12.22
2011-01-23   0.00   2.22    10.00
2011-01-24  16.00   0.00    16.00
2011-01-24   3.76   0.00    19.76
2011-01-24   0.00   5.50    14.26

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.

Answer 3

Any 'normal' operation involving a null yields null.

Read about COALESCE; use the fact that COALESCE(foo, 0) returns foo if it is not null and returns 0 if foo is null.