Eric,
I'd replace ....
(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;
with ...
(IF(avgTest.Q1<7,avgTest.Q1,0) + IF(avgTest.Q2<7,avgTest.Q2,0) +
IF(avgTest.Q3<7,avgTest.Q3,0)+
(IF(avgTest.Q1<7,avgTest.Q1,0)+ IF (avgTest.Q2<7,avgTest.Q2,0)+ IF
(avgTest.Q3<7,avgTest.Q3,0)+
IF(avgTest.Q4<7,avgTest.Q4,0) + IF(avgTest.Q5<7,avgTest.Q5,0)) /
MAX(1,IF(avgTest.Q1<7,1,0) + IF(avgTest.Q2<7,1,0) + IF(avgTest.Q3<7,1,0)
+ IF(avgTest.Q4<7,1,0) + IF(avgTest.Q5<7,1,0))
PB
----
Eric Lommatsch wrote:
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] <mailto:[EMAIL PROTECTED]>
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.175 / Virus Database: 270.8.5/1764 - Release Date: 11/3/2008 7:46 AM
