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
>
> ericl@stripped <mailto:ericl@stripped>
>
>
>
> ------------------------------------------------------------------------
>
>
> 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
>
>
