AVE of rows with nulls 1

[tinapa]

hi guys, AVE is only for columns? can i use this in rows? if i have this sample how can i have the sql server calculate this:

average of rows with null

id col1 col2 col3 col4
1 1 2 3 4
2 null 1 7 7
3 1 null null 9
4 3 2 1 6


to this?

for id=1:
(1+2+3+4)/4


for id=2:
(1+7+7)/3

for id=3:
(1+9)/2

for id=4:
(3+2+1+6)/4

thanks for any inputs

 

[bborissov]

No. AVG() is only for rows. You must do your own calculations here:

SELECT (ISNULL(col1,0)+ISNULL(col2,0)+ISNULL(col3,0)+ISNULL(col4,0))/
(CASE WHEN col1 IS NULL 
      THEN 0 ELSE 1 END +
 CASE WHEN col2 IS NULL 
      THEN 0 ELSE 1 END +
 CASE WHEN col3 IS NULL 
      THEN 0 ELSE 1 END +
 CASE WHEN col4 IS NULL 
      THEN 0 ELSE 1 END)
FROM ....

Or:
SELECT Id, AVG(Columns) AS Ave
FROM (SELECT Id, Col1 AS Columns
FROM ....
UNION ALL
SELECT Id, Col2 AS Columns
FROM ....
UNION ALL
SELECT Id, Col3 AS Columns
FROM ....
UNION ALL
SELECT Id, Col4 AS Columns
FROM ....)
GROUP BY Id
[/code]

BOTH are NOT tested!!!




Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[tinapa]

hi Borislav, YOU ARE AWESOME!!!!

IT WORKED!

 

[tinapa]

BIG THANKS!