April 14, 2003 at 6:55 am
I am really new to SQLServer so apologies if the answer to this is obvious.
I am moving an Access (.mdb) file to Access Project (.adp). I have many numeric columns which I wish to average across the row (I want the totals as well). I can't find an easy way (this is not an aggregrate query) and only + - * / seem to be available. In Access it's easy, eg:
Sum1: Sum(NZ([E1])+NZ([M1])+...
Count1: (Count([E1])+Count([M1])+...
Avg1: IIf([count1]=0,Null,[sum1]/[count1])
But, in an SQL query so far I have only got:
ISNULL(E1,0) + ISNULL(M1,0)+... AS SUM1
ISNULL(E1/E1,0) + ISNULL(M1/M1,0)+... AS COUNT1
These both work but:
AVG1 = IF...ELSE...SUM1/COUNT1?
I cannot get the syntax right for calculating the averages, taking into account zero values for COUNT1. Is IIF available?
Can anyone help - or is there an easier way? I have almost completed the remainder of the conversion!
Nick
April 17, 2003 at 2:06 pm
Perhaps I was a little too hopeful - maybe I put the question in the wrong area.
However, I managed a work-around by placing the calculations in the Access Reports (where they were needed really), and out of the SQLServer environment. This involved too much copying and pasting of formulae though.
What I really wanted was trapping 'Division by zero' errors to return NULL values but I didn't know how.
Anybody any ideas?
Nick
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply