| Author |
Topic |
|
ddobbertin
Yak Posting Veteran
51 Posts |
 Posted - 2006-12-01 : 03:03:42
|
| I have a table that has answers ranging in point value from 0 to 5 for a survey that users have submitted. The basic record structure shows the name of the person and each of their 14 scores, 1 for each survey question. I need to be able to query the table and return the total average score per person. The trick is I need to be able to not count the scores that are 0. For example:User1,5,5,5,5,5,5,5,5,5,5,5,5,5,5 = average score is 5 ((5*14)/14)User2,5,5,5,5,5,5,5,5,5,5,5,5,5,0 = average score is 5 ((5*13)/13)Notice the second user has a zero for the last survey question? That changes their total # of answers to 13 instead of 14. I would have designed the whole thing differently but I didn't design it, I inherited it. Thanks in advance, you guys/gals always come through. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-12-01 : 06:00:30
|
[code]create table table1( [user] varchar(10), scores1 int, scores2 int, scores3 int, scores4 int, scores5 int)gocreate view view1as select [user], score = scores1 from table1 union all select [user], score = scores2 from table1 union all select [user], score = scores3 from table1 union all select [user], score = scores4 from table1 union all select [user], score = scores5 from table1 goinsert into table1select 'user1', 5, 5, 5, 4, 5 union allselect 'user2', 5, 5, 5, 0, 5select [user], avg_score = avg(nullif(convert(decimal(10,2), score), 0))from view1group by [user][/code]
KH |
 |
|
|
|
|
|
Need some kind of array in query????