Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

AVE of rows with nulls 1

Status
Not open for further replies.

tinapa

Technical User
Nov 12, 2008
81
GB
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

 
No. AVG() is only for rows. You must do your own calculations here:
Code:
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.
 
hi Borislav, YOU ARE AWESOME!!!!

IT WORKED!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top