Average counts NUll

Question

Average counts NUll

sharonsql2013

SSCertifiable

Points: 5908
More actions
August 30, 2014 at 9:52 am

#298962

I am trying to do an average with the following case statement
Select
AVG(Case when TotalHours < = 60 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours < = 45 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours < = 20 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours > 60 And TotalHours > 0 then TotalHours else 0 end)AVGover60
From
xyz
The situation is that the data has lots of rows were TotalHours is Null , So, even if there are two rows say within 60 hours which has avg value as 50 and 55 , instead of showing the average as 52. I get result as 3. Because its doing a count of all rows (Including the NULL ones).How can I fix this situation.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

sharonsql2013 (8/30/2014)
I am trying to do an average with the following case statement
Select
AVG(Case when TotalHours < = 60 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours < = 45 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours < = 20 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours > 60 And TotalHours > 0 then TotalHours else 0 end)AVGover60
From
xyz
The situation is that the data has lots of rows were TotalHours is Null , So, even if there are two rows say within 60 hours which has avg value as 50 and 55 , instead of showing the average as 52. I get result as 3. Because its doing a count of all rows (Including the NULL ones).How can I fix this situation.

Quick thought, in a CTE, filter out the NULL values before the aggregation.

😎

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Eirikur Eiriksson (8/30/2014)
sharonsql2013 (8/30/2014)
I am trying to do an average with the following case statement
Select
AVG(Case when TotalHours < = 60 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours < = 45 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours < = 20 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours > 60 And TotalHours > 0 then TotalHours else 0 end)AVGover60
From
xyz
The situation is that the data has lots of rows were TotalHours is Null , So, even if there are two rows say within 60 hours which has avg value as 50 and 55 , instead of showing the average as 52. I get result as 3. Because its doing a count of all rows (Including the NULL ones).How can I fix this situation.
Quick thought, in a CTE, filter out the NULL values before the aggregation.
😎

Perhaps a simpler approachis easier: just change "From xyz" to "From xyz where TotalHours is not NULL".

Of course with all those missing commas it's surprising it's not just giving a syntax error instead of producing 3 as an answer (must be some language other than T-SQL) :w00t:. I don't much like 3 columns in the result set having the same name, either. :hehe:

Tom

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

TomThomson (8/30/2014)
Eirikur Eiriksson (8/30/2014)
sharonsql2013 (8/30/2014)
I am trying to do an average with the following case statement
Select
AVG(Case when TotalHours < = 60 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours < = 45 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours < = 20 And TotalHours > 0 then TotalHours else 0 end)AVGWithin60
AVG(Case when TotalHours > 60 And TotalHours > 0 then TotalHours else 0 end)AVGover60
From
xyz
The situation is that the data has lots of rows were TotalHours is Null , So, even if there are two rows say within 60 hours which has avg value as 50 and 55 , instead of showing the average as 52. I get result as 3. Because its doing a count of all rows (Including the NULL ones).How can I fix this situation.
Quick thought, in a CTE, filter out the NULL values before the aggregation.
😎
Perhaps a simpler approachis easier: just change "From xyz" to "From xyz where TotalHours is not NULL".
Of course with all those missing commas it's surprising it's not just giving a syntax error instead of producing 3 as an answer (must be some language other than T-SQL) :w00t:. I don't much like 3 columns in the result set having the same name, either. :hehe:

+1

😎