List: General Discussion « Previous MessageNext Message » From: Peter Brawley Date: November 4 2008 8:14pm Subject: Re: Question about Averaging IF() function results View as plain text
```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
> 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
>
>

```