RE: Question about Averaging IF() function results

[Eric Lommatsch]

Hello Peter,
 
Thanks for your suggestion, I think I have found another way to get the
average that I need. 
 
If the formula I have come up with does not work I will try your formula.
 
Thank you
 
Eric H. Lommatsch
Programmer
360 Business 
2087 South Grant Street
Denver, CO 80210
Tel [phone]
Fax [phone]
 
ericl@stripped
 

________________________________

From: Peter Brawley [mailto:peter.brawley@stripped] 
Sent: Tuesday, November 04, 2008 1:14 PM
To: Eric Lommatsch
Cc: mysql@stripped
Subject: Re: Question about Averaging IF() function results


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 [phone]
	Fax [phone]
	 
	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