AVG across columns - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

AVG across columns

Author

Topic

jmcdaniels
Starting Member

4 Posts

Posted - 2011-04-19 : 12:14:21

There are other threads here that come close to answering this question, but none I can make work. Here's my scenario.

Sample Data
ID Survey Q1 Q2 Q3 Comment
18 001 5 0 3 Test 1
19 002 0 2 4 Test 2

I need to select the average for all the surveys, all questions, in the table while discarding 0 selections.
For instance survey 001 has an average of 4 discarding the 0 and survey 002 has an average of 3. I need to be able to write a query that will give me an average for all the surveys - in this case roughly 3.5

Did I explain this well enough for anyone to understand what I need?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-19 : 12:22:29

SELECT ID, Survey, (Q1+Q2+Q3)/(SIGN(Q1)+SIGN(Q2)+SIGN(Q3)) Average
FROM myTable

jmcdaniels
Starting Member

4 Posts

Posted - 2011-04-19 : 12:34:21

Great start, thank you. Now how would I deal with Divide by zero errors?

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-19 : 12:51:23

SELECT ID, Survey, (Q1+Q2+Q3)/(SIGN(Q1)+SIGN(Q2)+SIGN(Q3)) Average
FROM myTable
WHERE Q1+Q2+Q3<>0

jmcdaniels
Starting Member

4 Posts

Posted - 2011-04-19 : 13:05:15

Brilliant! That's almost it. Is there a way to roll all the averages up into one average? Right now it's yielding:

5
5
5
5
4
3
3
4
3

I'd love to see it automatically calculate 4.11

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-19 : 13:11:58

SELECT AVG((Q1+Q2+Q3)/(SIGN(Q1)+SIGN(Q2)+SIGN(Q3))/1.) Average
FROM myTable
WHERE Q1+Q2+Q3<>0

jmcdaniels
Starting Member

4 Posts

Posted - 2011-04-19 : 13:18:06

Outstanding. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources