Avg

Hi,

right now say I have data that looks like such

Name data1 data2 data3
a 1 2 3
b 3 5 2
c 1 7 9

what I would like to do is obtain the average from these queries, but with one slight condition

in all the data sets, while calculating the average, I do not want it to calculate the average if it encounters 3 in any of the columns.
the problem here is if I tell it to ignore 3 in 'data1', it will ignore row 2 in all of the other columns as well.

your question is a bit ambiguous, but i'll take a stab at it

select sum(iif(data1=3,0,data1)) / sum(iif(data1=3,0,1)) as avg1
, sum(iif(data2=3,0,data2)) / sum(iif(data2=3,0,1)) as avg2
, sum(iif(data3=3,0,data3)) / sum(iif(data3=3,0,1)) as avg3
from yourtable

this calculates the (column) averages, ignoring any 3s

rudy
http://r937.com/

thank you..

I will try that..
It seems that the SQL I learned doesn't seem to suffice my needs..
I went on www.sqlcourse.com and went through the whole basic and advanced queries tutorial.. but have never come across iif

where did u learn ur SQL ??

iif is not SQL, its Visual Basic code and will only work on MS Access or maybe SQL Server.

thanks, a.koepke, i knew that, but ecit12 is running access, as you can see in this other thread: http://forums.devshed.com/t64389/s.html

rudy

Yeah, I wasn't correcting or commenting on your solution, Just making sure that ecit12 knew it wasn't SQL, from his post it seems that he didn't

thanks guys for helping me out..

r937, the code u provided me works perfectly.
many thanks to u..

thanks a.koepke for letting me know that its not SQL..
for a minute there, I thought I have so much more SQL to learn..

ok... I'm encountering another problem here..

here is my main statement

Sum(IIf((ServiceOutput.A1>-1) And (ServiceOutput.A1<11),ServiceOutput.A1,0))/Sum(IIf((ServiceOutput.A1>-1) And (ServiceOutput.A1<11),1,0)) AS AvgOfA1


what that piece of code does is basically calculate the average the way r937 instructed me to.. and the method works well.. now, in some cases, the average of the column can't be calculated because all the data values fall outside the range

eg

Col1

99
99
99
99

etc..

so when it outputs a message, it gives be me an error message.

what I want to do is tell it that if the average is not between 1 and 10, the value of the average is 99 (a special value)

however, if I write it out.. my code increases by 300%
this is how I would write it out, but it doesn't seem like a good idea.

iif((
Sum(IIf((ServiceOutput.A1>-1) And (ServiceOutput.A1<11),ServiceOutput.A1,0))/Sum(IIf((ServiceOutput.A1>-1) And (ServiceOutput.A1<11),1,0)))>-1 AND (Sum(IIf((ServiceOutput.A1>-1) And (ServiceOutput.A1<11),ServiceOutput.A1,0))/Sum(IIf((ServiceOutput.A1>-1) And (ServiceOutput.A1<11),1,0)))<11), Sum(IIf((ServiceOutput.A1>-1) And (ServiceOutput.A1<11),ServiceOutput.A1,0))/Sum(IIf((ServiceOutput.A1>-1) And (ServiceOutput.A1<11),1,0)), 0)

or
iif(AvgCalculated>-1 AND AvgCalculated<11, AvgCalculated, 0)
any suggestions..??