Average of columns for a single row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Average of columns for a single row

Author

Topic

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-03-29 : 17:24:39

Hi there, I'm having trouble getting an average. Here's what my table basically looks like:


ID    Q1    Q2    Q3    Q4    Q5
83    NULL  10    5     9     NULL

I'd like to add a column that takes the average of the question fields (Q1 through Q5), ignoring NULLS, so that ID 83 should have an average of (10 + 5 + 9)/3 = 8. I know AVG() will do it along a single column, but what's the name of the function that does this for a single row?

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-29 : 17:40:43

SELECT ID,
(IsNull(Q1,0) + IsNull(Q2,0) + IsNull(Q3,0) + IsNull(Q4,0) + IsNull(Q5,0) ) / (
CASE WHEN Q1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Q2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Q3 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Q4 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Q5 IS NULL THEN 0 ELSE 1 END)
FROM myTable

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-03-29 : 18:01:31

Yikes, I thought there'd be a function for that. Thank you!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-03-29 : 18:07:15

[code]select
ID,
[MyAverage] =
(
select
X1= avg(bb.xx)
from
(
select xx = a.Q1 where a.Q1 is not null union all
select xx = a.Q2 where a.Q2 is not null union all
select xx = a.Q3 where a.Q3 is not null union all
select xx = a.Q4 where a.Q4 is not null union all
select xx = a.Q5 where a.Q5 is not null
) bb
)
from
MyTable[/code]

More on both methods here:
MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-03-29 : 18:11:15

quote:
Originally posted by robvolk

SELECT ID,
(IsNull(Q1,0) + IsNull(Q2,0) + IsNull(Q3,0) + IsNull(Q4,0) + IsNull(Q5,0) ) / (
CASE WHEN Q1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Q2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Q3 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Q4 IS NULL THEN 0 ELSE 1 END +
CASE WHEN Q5 IS NULL THEN 0 ELSE 1 END)
FROM myTable

Probably needs a check for the case when Q1 thru Q5 are all null to avoid a divide by zero error.

SELECT
	ID, 
	(IsNull(Q1,0) + IsNull(Q2,0) + IsNull(Q3,0) + IsNull(Q4,0) + IsNull(Q5,0) ) /
	(
	NULLIF(0,
	CASE WHEN Q1 IS NULL THEN 0 ELSE 1 END +
	CASE WHEN Q2 IS NULL THEN 0 ELSE 1 END +
	CASE WHEN Q3 IS NULL THEN 0 ELSE 1 END +
	CASE WHEN Q4 IS NULL THEN 0 ELSE 1 END +
	CASE WHEN Q5 IS NULL THEN 0 ELSE 1 END)
	)
FROM
	MyTable

CODO ERGO SUM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-29 : 20:26:52

There's also UNPIVOT:

select id, AVG(Q)
from (select * from myTable) a
unpivot(Q for QQ IN(Q1,Q2,Q3,Q4,Q5)) b
group by id

If all the columns are null, it won't return anything though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources