From: Peter Brawley
Date: November 4 2008 8:14pm
Subject: Re: Question about Averaging IF() function results
List-Archive: http://lists.mysql.com/mysql/215128
Message-Id: <4910AD24.8040102@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------060107060403030104050508"

--------------060107060403030104050508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>   

--------------060107060403030104050508--