Null Calculation with SQL Select Statement

Question

I have the following table

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

If I pass the following Sql statement:

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

So the output is:

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

If I need an output like below:

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

What is SQL Transact Statement?

My table structure is below:

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

Note. - I applied the Group By function on the input and output columns , which does not make any difference, since there are transID columns that are auto-increments, therefore, this should be displayed all the rows of the table.

Answer 1

I think this might work for you. First, here is how I defined my test pattern 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 b.[date] = a.[date].

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;

:

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

, , . , , , .

Answer 2

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 3

"" null.

COALESCE; , COALESCE(foo, 0) foo, null, 0, foo null.

Answer 4

Here is the Sql-2000 server solution that I want to share with the community next to the arcain Solution :

Here I use Coalesce instead of ISNULL

select
transID,
input,
output,
runningtotal =  coalesce(input,0)- coalesce(output,0) +
coalesce(
(select
sum(coalesce(input,0)-coalesce(output,0))
from stock b
where b.transID < a.transID
),0)
from stock a
order by transID

Thank.......