Hello List,
I have a question about trying to calculate an average across columns. I am
trying to calculate the results of surveys where in the data I have
individuals that have marked questions on the survey as N/A. in my survey I
am using 1-6 as the evaluated answers and if the person marked NA the stored
value is 7.
Here is a table with some sample data of what I am using to test the
calculation I am working on: ( actually this is simplified from the actual
data but the results I get are still the same)
CREATE TABLE `avgTest` (
`Course` varchar(8) default NULL,
`Q1` int(11) default NULL,
`Q2` int(11) default NULL,
`Q3` int(11) default NULL,
`Q4` int(11) default NULL,
`Q5` int(11) default NULL
)
Course | Q1 | Q2 | Q3 | Q4 | Q5
-----------------------------------------------------------------------------
----
HUM300 | 6 | 6 | 7 | 6 | 6
HUM301 | 6 | 6 | 6 | 6 | 6
HUM301 | 7 | 7 | 7 | 7 | 7
Here is the query that I am using to perform the calculations
select course,
avg(IF(avgTest.Q1<7,avgTest.Q1,Null)) as AvgOfQ1,
avg(IF(avgTest.Q2<7,avgTest.Q2,Null)) as AvgOfQ2,
avg(IF(avgTest.Q3<7,avgTest.Q3,Null)) as AvgOfQ3,
avg(IF(avgTest.Q4<7,avgTest.Q4,Null)) as AvgOfQ4,
avg(IF(avgTest.Q5<7,avgTest.Q5,Null)) as AvgOfQ5,
(avg(IF(avgTest.Q1<7,avgTest.Q1,Null))
+avg(IF(avgTest.Q2<7,avgTest.Q2,Null))
+avg(IF(avgTest.Q3<7,avgTest.Q3,Null))
+avg(IF(avgTest.Q4<7,avgTest.Q4,Null))
+avg(IF(avgTest.Q5<7,avgTest.Q5,Null)))/5 as overallAvg from avgTest group
by course;
Here are the results that I get that are incorrect.
Course | AvgOfQ1 | AvgOfQ2 | AvgOfQ3 | AvgOfQ4 |
AvgOfQ5 | overallAvg
-----------------------------------------------------------------------------
----------------------------------------------------------------------
HUM300 | 6.000 | 6.000 | Null |
6.000 | 6.000 | Null
HUM301 | 6.000 | 6.000 | 6.000 | 6.000
| 6.000 | 6.000
Here are the results that I get that when I change using null in the query to
a 0.
Course | AvgOfQ1 | AvgOfQ2 | AvgOfQ3 | AvgOfQ4 |
AvgOfQ5 | overallAvg
-----------------------------------------------------------------------------
----------------------------------------------------------------------
HUM300 | 6.000 | 6.000 | 0.000 | 6.000
| 6.000 | 4.800
HUM301 | 6.000 | 6.000 | 6.000 | 6.000
| 6.000 | 6.000
Here are the results that I want to be getting from the query that I am
working with.
Course | AvgOfQ1 | AvgOfQ2 | AvgOfQ3 | AvgOfQ4 |
AvgOfQ5 | overallAvg
-----------------------------------------------------------------------------
----------------------------------------------------------------------
HUM300 | 6.000 | 6.000 | Null |
6.000 | 6.000 | 6.000
HUM301 | 6.000 | 6.000 | 6.000 | 6.000
| 6.000 | 6.000
I tried using the if function without a false answer and I am getting a
syntax error when I do this.
If it is possible for me to get this correct result in MySQL, can someone
provide me with the correct query syntax to get these results?
Thank you
Eric H. Lommatsch
Programmer
360 Business
2087 South Grant Street
Denver, CO 80210
Tel 303-777-8939
Fax 303-778-0378
[EMAIL PROTECTED]
