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.

Looks like you want a running totals calculation? If so what are you ordering by? (The NULL bit is easy - just use COALESCE) — Martin Smith
– Martin Smith, Commented Jan 22, 2011 at 12:53

Martin Smith · Accepted Answer · 2011-01-22 13:06:41Z

4

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

answered Jan 22, 2011 at 13:06

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Andomar Over a year ago

It's unlikely that grouping on Input,Output is correct, he probably wants to retain the transaction date ordering. I'd upvote this if you omit the sum and group by.

mahesh Over a year ago

@Martin, Here is require @runbal variable but if we create bal1 extra columns for store the rest of calculation and than after divide input and ouput. is it possible?

Martin Smith Over a year ago

@Andomar - I agree this query seems to have no practical application but the OP needs to clarify this @mahesh Can you expand your question to tell us your actual table schema and add a greater amount of sample data and desired results? This gives you your desired results but they don't seem to make much sense and now you seem to be changing them anyway!

mahesh Over a year ago

@Martin, I have tested your code but it's simply demand "@runbal" Variable even though you declared it.

Martin Smith Over a year ago

@mahesh - Can you confirm that you are actually on MySQL as per the tags? Not Microsoft SQL Server as I see you mention Transact SQL?

|

arcain · Accepted Answer · 2011-09-26 13:59:39Z

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.

9000 · Accepted Answer · 2011-01-22 12:57:21Z

2

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.

answered Jan 22, 2011 at 12:57

9000

41k9 gold badges69 silver badges108 bronze badges

Comments

mahesh · Accepted Answer · 2011-01-29 09:53:15Z

0

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.......

answered Jan 29, 2011 at 9:53

mahesh

1,39010 gold badges36 silver badges61 bronze badges

Comments

Masoud R · Accepted Answer · 2020-02-20 08:25:22Z

0

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

answered Feb 20, 2020 at 8:25

Masoud R

1143 silver badges10 bronze badges

Collectives™ on Stack Overflow

Null Calculation With SQL Select Statement

5 Answers 5

8 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related