CODEWITHSUNDEEP
sqlsql-servert-sql
mahesh
mahesh

Reputation: 1390

Null Calculation With SQL Select Statement

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.

Upvotes: 5

Views: 10106

Answers (5)

Masoud R
Masoud R

Reputation: 114

Using arcain sample table you can use following query that take advantage of window functioning to get the job done.

--arcain sample table 

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);

Query

SELECT *,
        SUM( COALESCE(input,0) - COALESCE(output,0) ) 
            OVER (PARTITION BY date ORDER BY date,transId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM @stock

Upvotes: 0

arcain
arcain

Reputation: 15270

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.

Upvotes: 3

mahesh
mahesh

Reputation: 1390

Here is Sql-2000 server Solution i wants to share with community which is near by arcain's Solution:

Here I Use Coalesce Function 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

Thanks.......

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453298

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

Upvotes: 4

9000
9000

Reputation: 40894

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.

Upvotes: 2

Related Questions