SQL query returning Null - 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

SQL query returning Null

Author

Topic

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-06-15 : 09:28:25

The following is supposed to check the month in the date column, and then check the denominator in a calculation I want to perform. As long as the denominator isn't zero it should perform the calculation and finally average the result of the calculation over account numbers thus producing a cross tab query.

For a reason unbeknownst to me it's returning Null for all account numbers:

Select AccountNo,
avg(case month(importdate) when 1 then (case (stxmonthfce + stxmonthcase) when 0 then null else
(stxmonthcase / (stxmonthfce + stxmonthcase)) end) end) as January,
avg(case month(importdate) when 2 then (case (stxmonthfce + stxmonthcase) when 0 then null else
(stxmonthcase / (stxmonthfce + stxmonthcase)) end) end) as February
From publications.tblchristask
Group by AccountNo

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-15 : 10:22:01

quote:
Originally posted by stoolpidgeon

The following is supposed to check the month in the date column, and then check the denominator in a calculation I want to perform. As long as the denominator isn't zero it should perform the calculation and finally average the result of the calculation over account numbers thus producing a cross tab query.

For a reason unbeknownst to me it's returning Null for all account numbers:

Select AccountNo,
avg(case month(importdate) when 1 then (case (stxmonthfce + stxmonthcase) when 0 then null else
(stxmonthcase / (stxmonthfce + stxmonthcase)) end) end) as January,
avg(case month(importdate) when 2 then (case (stxmonthfce + stxmonthcase) when 0 then null else
(stxmonthcase / (stxmonthfce + stxmonthcase)) end) end) as February
From publications.tblchristask
Group by AccountNo

This can happen if some of the columns are null and others are null in your addition operations. For example, "stxmonthfce + stxmonthcase". Change that to ISNULL(stxmonthfce,0) + ISNULL(stxmonthcase,0).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources