Preventing Null when totaling computed cols

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

SQL Server 2000 Forums

Transact-SQL (2000)

Preventing Null when totaling computed cols

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2004-07-22 : 11:45:45

Situation is I'm summing a bunch of columns from different tables and grouping everything together on a ssn. Using a left join I'm getting null in cols that do not have a coresponding link. I then use the case stmt to check for a null and if so replace with a zero. Now I'd like to add all the columns together but I'm getting a null value if any of the columns had a null but where changed to zero. Attached is the code.

SELECT
dbo.tblFacInfo.[Last Name], dbo.tblFacInfo.[First Name], dbo.tblFacInfo.SSN,
case
when a1.IncentivePaymentSum is Null then 0 else a1.IncentivePayMentSum
end as IncentivePaymentSum,
case
when a2.AcademicPaymentSum is Null then 0 else a2.AcademicPaymentSum
end as AcademicPaymentSum,
case
when a3.ResearchPaymentSum is Null then 0 else a3.ResearchPaymentSum
end as ResearchPaymentSum,
case
when a4.SuppPaymentSum is Null then 0 else a4.SuppPaymentSum
end as SuppPaymentSum,
--Add all Values together but get error if any col has a null
a1.IncentivePaymentSum + a2.AcademicPaymentSum + a3.ResearchPaymentSum +a4.SuppPaymentSum as Total

FROM dbo.tblFacInfo LEFT OUTER JOIN
(SELECT SSN, SUM(AmountPd) AS AcademicPaymentSum
FROM dbo.tblAcademicPmts
GROUP BY SSN) a2 ON dbo.tblFacInfo.SSN = a2.SSN LEFT OUTER JOIN
(SELECT SSN, SUM(AmountPd) AS IncentivePaymentSum
FROM dbo.tblIncentivePmts
GROUP BY SSN) a1 ON dbo.tblFacInfo.SSN = a1.SSN LEFT OUTER JOIN
(SELECT SSN, SUM(AmountPd) AS ResearchPaymentSum
FROM dbo.tblResearchPmts
GROUP BY SSN) a3 ON dbo.tblFacInfo.SSN = a3.SSN LEFT OUTER JOIN
(SELECT SSN, SUM(AmountPd) AS SuppPaymentSum
FROM dbo.tblSuppPmts
GROUP BY SSN) a4 ON dbo.tblFacInfo.SSN = a4.SSN

order by dbo.tblFacInfo.SSN

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 11:53:37

all formulas reference the original columns, not the new alias's or formulas you have calculated. so, you need to do the calculations more twice if you'd like to reference it twice.

A handy trick for you is to use the ISNULL() function like so:

SELECT
dbo.tblFacInfo.[Last Name], dbo.tblFacInfo.[First Name], dbo.tblFacInfo.SSN,
ISNULL(a1.IncentivePaymentSum,0) as IncentivePaymentSum,
ISNULL(a2.AcademicPaymentSum,0) as AcademicPaymentSum,
ISNULL(a3.ResearchPaymentSum,0) as ResearchPaymentSum,
ISNULL(a4.SuppPaymentSum,0) as SuppPaymentSum,

--Add all Values together but get error if any col has a null
ISNULL(a1.IncentivePaymentSum,0) + ISNULL(a2.AcademicPaymentSum,0) +
ISNULL(a3.ResearchPaymentSum,0) + ISNULL(a4.SuppPaymentSum,0) as Total

another option, by the way is to use a subquery:

select a.*, IncentivePaymentSum+AcademicPaymentSum+ResearchPaymentSum+ResearchPaymentSum as Total
from
(

-- your query here, w/o the total calculated --

) a

Then, in this case, you can reference the new alias's since they live in the subquery. I hope this makes sense.
- Jeff

bogey
Posting Yak Master

166 Posts

Posted - 2004-07-22 : 13:39:11

Jeff,
Worked like a charm :)

thanks

Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources