sum without nulls(Solved) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum without nulls(Solved)

Author

Topic

puvpul
Starting Member

6 Posts

Posted - 2011-10-17 : 05:05:55

I’m trying to create view/sp that would sum current value with two other values. Few of the data for current period value are not available. In this case it will take previous one to the previous one. example are shown below:
CREATE TABLE nprofit (
ticker varchar(16) NOT NULL,
pyear int NOT NULL,
amount decimal(12,2) DEFAULT NULL,
PRIMARY KEY (ticker,pyear)
)
go
INSERT nprofit
VALUES
('ABBANK', 2011, '20'),
('ALARA', 2011, null),
('BASIA', 2011, null)
go
CREATE TABLE nprofit10 (
ticker varchar(16) NOT NULL,
pyear int NOT NULL,
amount decimal(12,2) DEFAULT NULL,
PRIMARY KEY (ticker,pyear)
)
go
INSERT nprofit10
VALUES
('ABBANK', 2010, '20'),
('ALARA', 2010, '10'),
('BASIA', 2010, '20')
CREATE TABLE nprofit09 (
ticker varchar(16) NOT NULL,
pyear int NOT NULL,
amount decimal(12,2) DEFAULT NULL,
PRIMARY KEY (ticker,pyear)
)
go
INSERT nprofit09
VALUES
('ABBANK', 2011, '20'),
('ALARA', 2011, '5'),
('BASIA', 2011,'l2')
CREATE TABLE nprofit08 (
ticker varchar(16) NOT NULL,
pyear int NOT NULL,
amount decimal(12,2) DEFAULT NULL,
PRIMARY KEY (ticker,pyear)
)
go
INSERT nprofit08
VALUES
('ABBANK', 2011, '20'),
('ALARA', 2011, '100'),
('BASIA', 2011, '50')
i have performed the following ticker, (a.amount+b.amount+c.amount)/3 as avggrw. because some data is not avilable in that case i need like this: tickr,(b.amount+c.amount+d.amount)/3.
i've tried the following way but its right result:
select a.ticker,
case when a.namount=null
then (b.namount+c.namoutn+d.namount)/3
else (a.nmount+b.namount+c.namount)/3
end as avg2
FROM netprofit a
LEFT JOIN netprofit10 b ON a.ticker = b.ticker
JOIN netprofit09 c ON b.ticker = c.ticker
JOIN netprofit08 d ON c.ticker = d.ticker
Please help me on this. as i'm new to sql programming

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-10-17 : 07:18:54

First off don't use '=null', use 'IS NULL'

I'm currently reading the rest and trying to understand

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-10-17 : 07:22:12

You can use isnull(table.column, 0)

This will cancel out nulls in your equation

For example


select 123 + 123 + null

null


select 123 + 123 + isnull(null, 0)

246

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 07:58:11

the reason is null is not stored as a value but its represented as a flag which indicates lack of value.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

puvpul
Starting Member

6 Posts

Posted - 2011-10-17 : 23:58:08

hi
i had similar problem before and visakh16 helped me on that. so i finally figured it. the solution is as follows:

select a.ticker,
(coalesce(
(a.amount+b.amount+c.amount),
(b.mount+c.amount+d.amount))/3) as avg3

FROM netprofit a
LEFT JOIN netprofit10 b ON a.ticker = b.ticker
LEFT JOIN netprofit09 c ON a.ticker = c.ticker
LEFT JOIN netprofit08 d ON a.ticker = d.ticker

thx visakh16 again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 01:45:22

no problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources